SQL Server : find recent consecutive records that are greater than 5

Question

I need to write a query that shows the result broken down by FormID that have a value greater than 5 based on the most recent LogDate.

Based on the most recent LogDate, if there was a value that was less than 5, it should display values from that point that are greater than 5 as the values under 5 is a 'reset' if you will.

I am essentially looking at recent consecutive LogDate records that are greater than 5.

Say we have the following record set:

FormID   Value  LogDate    
--------------------------
Form2    6      10/12/19   
Form2    7      10/13/19   
Form1    8      10/12/19
Form1    12     10/12/19
Form1    3      10/14/19
Form1    8      10/15/19
Form1    6      10/21/19  

The following would return the following (please note I like to show the row_num as well:

 FormID   Value  LogDate   row_num
 ----------------------------------
 Form2    6      10/12/19  1
 Form2    7      10/13/19  2
 Form1    8      10/15/19  1
 Form1    6      10/21/19  2

Note in the example above, since the following record had a recent value under 5 (value of 3), we need to get the records that are above 5.

Another example:

FormID   Value  LogDate     
Form1    8      10/15/19
Form1    3      10/21/19  

RESULT: No result would be shown as there are in recent record that is greater than 5

Another example:

FormID   Value  LogDate    
Form2    4      10/12/19   
Form2    3      10/13/19   
Form1    16     10/12/19
Form1    3      10/12/19
Form1    3      10/14/19
Form1    8      10/15/19
Form1    12     10/21/19 

Result here would be:

FormID   Value  LogDate   row_num
Form1    8      10/15/19  1
Form1    12     10/21/19  2

Another example:

FormID   Value  LogDate    
Form1    12      10/12/19   
Form2    13      10/13/19  

Result:

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

From my understanding, this can be done with the LAG function but not sure how to put it altogether.

We can do something like the following:

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

    select t.*, 
        lag(value) over(partition by formid order by logdate) lag_value
    from @mytablet

But not sure how to pull it all together.

Answer 1

If I follow you correctly, you can do this with window functions like this:

select 
from (
    select t.*, 
        row_number() over(partition by formid order by logdate desc) rn,
        sum(case when value > 5 then 1 else 0 end) over(partition by formid order by logdate desc) grp
    from mytable t
) t
where rn = grp

The idea is to compare the number of values above 5 to a row number, counting from the most recent value. Rows where the two values are equal can be retained.