0

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'
SeanC
  • 15,695
  • 5
  • 45
  • 66

0 Answers0