Count if a value appears in consecutive rows

Question

I've got this table:

  ID           Date            Event
  ----------------------------------------
  123        2022-05-01         OCT
  123        2022-05-04         OCT
  123        2022-05-05         OCT
  123        2022-05-07         OCT
  123        2022-05-08         GRE
  123        2022-05-10         GRE
  123        2022-05-12         OCT
  123        2022-05-15         OCT

What I wish is to count the number of events appearing in a row, in order to check if the event 'OCT' has happened 4 times in a row. for example:

Desirable output:

  ID           Date            Event         Order_Event
  --------------------------------------------------------
  123        2022-05-01         OCT               1
  123        2022-05-04         OCT               2
  123        2022-05-05         OCT               3
  123        2022-05-07         OCT               4
  123        2022-05-08         GRE               1
  123        2022-05-10         GRE               2
  123        2022-05-12         OCT               1  
  123        2022-05-15         OCT               2

What I have tried is the row number() but it gives me that:

  ID           Date            Event         Order_Event
  --------------------------------------------------------
  123        2022-05-01         OCT               1
  123        2022-05-04         OCT               2
  123        2022-05-05         OCT               3
  123        2022-05-07         OCT               4
  123        2022-05-08         GRE               1
  123        2022-05-10         GRE               2
  123        2022-05-12         OCT               5  
  123        2022-05-15         OCT               6

you see? I don't want it to count the events 'OCT' in the 12th and 15th of may as five and six.. i want to see only if each event happened 4 times in a row (by date)

Thank you!

Answer 1

Try this:

DECLARE @DataSource TABLE
(
    [ID] INT
   ,[Date] DATE
   ,[Event] VARCHAR(3)
);

INSERT INTO @DataSource ([ID], [Date], [Event])
VALUES (123, '2022-05-01', 'OCT')
      ,(123, '2022-05-04', 'OCT')
      ,(123, '2022-05-05', 'OCT')
      ,(123, '2022-05-07', 'OCT')
      ,(123, '2022-05-08', 'GRE')
      ,(123, '2022-05-10', 'GRE')
      ,(123, '2022-05-12', 'OCT')
      ,(123, '2022-05-15', 'OCT');

SELECT [ID], [Date], [Event]
      ,ROW_NUMBER() OVER (PARTITION BY grp ORDER BY [Date]) AS [ Order_Event]
FROM
(
    SELECT *
          ,ROW_NUMBER() OVER(ORDER BY [Date]) - ROW_NUMBER() OVER(ORDER BY [Event], [Date]) AS grp
    FROM @DataSource
) DS
ORDER BY [Date];