Find the Count of Consecutive Occurrence in Table using SQL-CodePudding

enter code here have a table like this in my SQl Server

  NTN        |   Channel          Date_Time
006175A      |   Yellow         15-9-2021 10:16:00:00
0061751      |   Green          15-9-2021 10:15:00:00
00617523     |   Red            15-9-2021 10:14:00:00
006175A      |   Green          15-9-2021 10:13:00:00
006175A      |   Green          15-9-2021 10:12:00:00
006175A      |   Green          15-9-2021 10:11:00:00
0061756      |   Red            15-9-2021 10:10:00:00
00617523     |   Green          16-9-2021 10:10:00:00
00617523     |   Green          16-9-2021 10:12:00:00

Now what I am trying to do is to display consecutive Green for my NTN column like this:

 NTN        |    Greens |  Max(Date_Time)          |   Min(Date_Time)
 006175A    |     3     |  15-9-2021 10:13:00:000  |   15-9-2021 10:11:00:00
 00617523   |     2     |  16-9-2021 10:12:00:000  |   16-9-2021 10:10:00:00

Can anyone please help me how to achieve this using SQL Server

CodePudding user response：

WITH
  sorted AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (    ORDER BY date_time
                      )
                        AS seq_id,
    ROW_NUMBER() OVER (PARTITION BY ntn, channel
                           ORDER BY date_time
                      )
                        AS ntn_channel_seq_id
  FROM
    your_table
)
SELECT
  ntn,
  COUNT(*),
  MIN(date_time),
  MAX(date_time)
FROM
  sorted
WHERE
  channel = 'Green'
GROUP BY
  ntn,
  seq_id - ntn_channel_seq_id
HAVING
  COUNT(*) > 1
ORDER BY
  ntn,
  seq_id - ntn_channel_seq_id

De mo with a series of slightly differing test sets:

If you had posted the CREATE TABLE and the code for any indexes, I'd have tried to make the code do something other than a full scan of the table. Of course, if you were to pre-select the data you needed into a Temp Table, then this final reporting code wouldn't need to be fettered by SARGability. It's one of many "Divide'n'Conquer" tricks in the world of reporting against large tables.