clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805477 fiddles created (40817 in the last week).

CREATE TABLE t ( [FormID] [nvarchar](25), [Value] [int], [LogDate] [DATE] )
 hidden batch(es)


INSERT INTO t ([FormID],[Value],[LogDate]) VALUES ('Form2', 6, CONVERT(date,'10/12/19')), ('Form2', 8, CONVERT(date,'10/13/19')), ('Form1', 16, CONVERT(date,'10/12/19')), ('Form1', 3, CONVERT(date,'10/12/19')), ('Form1', 3, CONVERT(date,'10/14/19')), ('Form1', 8, CONVERT(date,'10/15/19')), ('Form1', 12, CONVERT(date,'10/21/19'))
7 rows affected
 hidden batch(es)


SELECT t.* FROM t LEFT JOIN (SELECT FormID, MAX(LogDate) AS recent_reset FROM t WHERE Value<6 GROUP BY FormID) AS reset_calendar ON t.FormID = reset_calendar.FormID WHERE t.LogDate > reset_calendar.recent_reset OR reset_calendar.recent_reset IS NULL
FormID Value LogDate
Form2 6 2019-10-12
Form2 8 2019-10-13
Form1 8 2019-10-15
Form1 12 2019-10-21
 hidden batch(es)


SELECT temp.*, ROW_NUMBER() OVER (PARTITION BY temp.FormID ORDER BY temp.LogDate) AS Sequence FROM ( SELECT t.* FROM t LEFT JOIN ( SELECT FormID, MAX(LogDate) AS recent_reset FROM t WHERE Value<6 GROUP BY FormID) AS reset_calendar ON t.FormID = reset_calendar.FormID WHERE t.LogDate > reset_calendar.recent_reset OR reset_calendar.recent_reset IS NULL)temp
FormID Value LogDate Sequence
Form1 8 2019-10-15 1
Form1 12 2019-10-21 2
Form2 6 2019-10-12 1
Form2 8 2019-10-13 2
 hidden batch(es)