Find records that occur for consecutive times

Question

I need to find all records that were greater than 5 but on a consecutive basis from the last time it was greater. Note that I can have different FormIDs.

    FormID   Value  LogDate
    Form1    6      10/12/19
    Form1    7      10/12/19
    Form1    4      10/14/19
    Form1    8      10/20/19
    Form1    9      10/21/19

In the example above, the result would be the following:

    FormID   Value  LogDate   row_num
    Form1    8      10/20/19  1
    Form1    9      10/21/19  2

As we had 2 records that were greater than 5 on a consecutive times.

Example 2:

    FormID   Value  LogDate
    Form1    6      10/12/19
    Form1    7      10/12/19
    Form1    6      10/14/19
    Form1    3      10/20/19
    Form1    9      10/21/19

In the example above, the result would be:

    FormID   Value  LogDate   row_num
    Form1    9      10/21/19  1

Example 3:

    FormID   Value  LogDate
    Form1    6      10/12/19
    Form1    7      10/12/19
    Form1    6      10/14/19
    Form1    3      10/20/19
    Form1    4      10/21/19

No value would be shown as after the value of 4 which is lower than 5, there were no recent records that were greater than 5.

Example 4:

    FormID   Value  LogDate
    Form2    6      10/12/19
    Form2    7      10/13/19
    Form1    6      10/12/19
    Form1    7      10/12/19
    Form1    6      10/14/19
    Form1    3      10/15/19
    Form1    6      10/15/19
    Form1    2      10/20/19

In this case, we should see the following:

    FormID   Value  LogDate   row_num
    Form2    6      10/12/19  1
    Form2    7      10/13/19  2

and nothing for Form1 as there was no data since that was over 5 since after that latest once which had a value of 2.

Below is a sample script to get started

   DECLARE @table1 TABLE
  (
   FormID VARCHAR(50), 
   [Value] INT, 
   LogDate DATETIME
  )

  INSERT INTO @table1
  VALUES 
  ('Form2',6,'10/12/19'),
  ('Form2',7,'10/13/19'),
  ('Form1',6,'10/12/19')  ,
  ('Form1',7, '10/12/19') ,
  ('Form1',6,'10/14/19') ,
  ('Form1',3,'10/15/19'),
  ('Form1', 4, '10/21/19'),
  ('Form1',6, '10/21/19'),
  ('Form1', 6, '10/21/19'),
  ('Form1', 2, '10/25/19')

 select FormID, 
 Value, LogDate,
 Row_number()
 OVER(
        PARTITION BY FormID
        ORDER BY LogDate) AS row_num
 from @table1

Answer 1

This reads like a gaps and island problem. Islands represents adjacent records with a value greater than 5, and you want all islands but the first.

Here is an approach using window functions; the logic is to identify the start of each island as a transition from a value below 5 to a value above 5:

select formid, value, logdate
from (
    select t.*, 
        sum(case when value > 5 and (lag_value <= 5 or lag_value is null) then 1 else 0 end)
            over(partition by formid order by logdate) flag
    from (
        select t.*, 
            lag(value) over(partition by formid order by logdate) lag_value
        from mytable t
    ) t
) t
where value > 5 and flag > 1