Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Which version of SQL-Server? This looks like a gaps-and-islands problem. –  ypercube Feb 21 at 15:17
    
SQL Server 2008 –  norgematos Feb 21 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 at 15:25
    
The rows do not have to be consecutive rows. And yes, different groups. –  norgematos Feb 21 at 15:30
2  
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 at 15:59
add comment

2 Answers 2

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 ;
share|improve this answer
add comment

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.

share|improve this answer
    
I added clarification. –  norgematos Feb 21 at 15:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.