Grouping by date range in a column

I have a table with data as:

``````ID    Action    Date
1     Action1   2014-01-20
2     Action1   2014-01-22
3     Action1   2014-01-28
4     Action2   2014-01-20
5     Action2   2014-01-22
6     Action2   2014-01-28
``````

I need to group by Date, grouping with 3 days of difference:

``````Action       Count
Action1      2
Action2      2
``````

If a record is grouped with a previous record, it won't be in another group:

``````ID    Action    Date
1     Action1   2014-01-20
2     Action1   2014-01-23
3     Action1   2014-01-24

Action       Count
Action1      2
``````

My solution:

``````SELECT T1.Action, DATEDIFF(DAY,T1.Date,T2.Date) as Count
FROM Table T1
JOIN Table T2 ON T1.Action=T2.Action
WHERE DATEDIFF(DAY,T1.Date,T2.Date) BETWEEN 1 AND 3
GROUP BY T1.Action,T1.Date,T2.Date
``````

Is this a correct solution?

-
Which version of SQL-Server? This looks like a gaps-and-islands problem. – ypercubeᵀᴹ Feb 21 '14 at 15:17
SQL Server 2008 – norgematos Feb 21 '14 at 15:19
So, if there is a difference of more than 3 days between 2 consecutive rows, then that count should count as a group change, right? – ypercubeᵀᴹ Feb 21 '14 at 15:25
The rows do not have to be consecutive rows. And yes, different groups. – norgematos Feb 21 '14 at 15:30
Your edit actually confuses more than clarifies. What if there are 12 days, one after the other, will that be 1 group? Or 4? – ypercubeᵀᴹ Feb 21 '14 at 15:59

In SQL-Server 2012 version, looks straightforward with the `LAG()` function:

``````SELECT
Action,
Number = COUNT(*)
FROM
( SELECT
Action,
Diff = DATEDIFF(day, LAG(Date) OVER (PARTITION BY Action ORDER BY Date), Date)
FROM
a
) AS t
WHERE
Diff > 3 OR Diff IS NULL
GROUP BY
Action ;
``````

This should work, even in 2008 version:

``````SELECT
Action,
Number = COUNT(*)
FROM
a
WHERE
NOT EXISTS
( SELECT *
FROM a AS b
WHERE b.Action = a.Action
AND ( (b.Date >= DATEADD(day, -3, a.Date) AND b.Date < a.Date)
OR (b.Date = a.Date AND b.ID < a.ID)
)
)
GROUP BY
Action ;
``````
-

Your requirements need some clarification. For instance, assuming you have 3 dates separated by 1 day each, would that count as 2 groups with count 1 and 2 respectively, 2 and 1 or even 2 and 2?

An easy solution - albeit possibly not what you had in mind would be to mod3 the dates and group by that.

-
I added clarification. – norgematos Feb 21 '14 at 15:47