The query I am trying to produce is very similar to this, but instead of counting where it changes, I need to count up to the point the difference exceeds a specific value.
I have tried setting a flag where the date difference is more than 14 from the previous, but that then gives 2 records - one for when the difference is greater than 14, and 1 for the rest. As the ID can appear multiple times with different dates, I cannot then group that result.
e.g. Data:
ID Date
1A 2020-01-01
1A 2020-01-03
2B 2020-01-05
1A 2020-02-01
Result set to be:
ID Date Count
1A 2020-01-01 2
2B 2020-01-05 1
1A 2020-02-01 1
the criteria in this case being where the difference between the dates is more than 14 days
Tried:
SELECT [ID], [Date],
case when datediff(dd,lag([Date]) over (partition by ID order by [Date]),[Date])>14
then 1 else 0 end as CaseValue
FROM Table
where [Date]>'2020-02-01'
and
declare @CountValue bigint
SELECT [ID], [Date],
@CountValue=@CountValue+case when datediff(dd,lag([Date]) over (partition by ID order by [Date]),[Date])>14
then 1 else 0 end
FROM Table
where [Date]>'2020-02-01'