0

I have below SQL database and would like to group them in sequence and assign ID to each group.

Time Line Colour
2021-11-02 3:00:00PM 1 Black
2021-11-02 3:00:01PM 1 White
2021-11-02 3:00:02PM 1 Red
2021-11-02 3:00:04PM 1 Red
2021-11-02 3:00:05PM 1 Black
2021-11-02 3:00:06PM 1 Black
2021-11-02 3:00:00PM 2 Black
2021-11-02 3:00:01PM 2 Black
2021-11-02 3:00:02PM 2 White
2021-11-02 3:00:03PM 2 White
2021-11-02 3:00:03PM 2 White
2021-11-02 3:00:03PM 2 Black
2021-11-02 3:00:03PM 2 Black

Result that I am looking for is

Time Line Colour Qty Group ID
2021-11-02 3:00:00PM 1 Black 1 1
2021-11-02 3:00:01PM 1 White 1 2
2021-11-02 3:00:02PM 1 Red 2 3
2021-11-02 3:00:04PM 1 Red 2 3
2021-11-02 3:00:05PM 1 Black 2 4
2021-11-02 3:00:06PM 1 Black 2 4
2021-11-02 3:00:00PM 2 Black 2 1
2021-11-02 3:00:01PM 2 Black 2 1
2021-11-02 3:00:02PM 2 White 3 2
2021-11-02 3:00:02PM 2 White 3 2
2021-11-02 3:00:03PM 2 White 3 2
2021-11-02 3:00:04PM 2 Black 2 3
2021-11-02 3:00:05PM 2 Black 2 3

Qty is basically # of same colour from line in a row.

Group ID is sequential ID for colour change by line.

I just couldn't figure out as it needs to be sequential in 'Time' then 'Line' columns and unable to aggregate.

Dale K
  • 25,246
  • 15
  • 42
  • 71
s9yu
  • 9
  • 1
  • 1
    While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Nov 02 '21 at 20:17

1 Answers1

1

Here is how you can do it:

SELECT * , COUNT(*) OVER (PARTITION BY Line, groupId) Qty
FROM (
    SELECT *
      , rank() OVER (PARTITION BY Line ORDER BY Insertdate) 
      - rank() OVER (PARTITION BY Line, colour ORDER BY Insertdate) AS GroupId
   FROM tablename
) t ORDER BY line, Insertdate

db<>fiddle here

Dale K
  • 25,246
  • 15
  • 42
  • 71
eshirvana
  • 23,227
  • 3
  • 22
  • 38