CODEWITHSUNDEEP
sqlsql-serverdatesql-server-2014
Bob Horn
Bob Horn

Reputation: 34325

Group Rows By Date Range

I'd like to know how many rows, for a user, exist within a certain number of seconds (like 10). So, given this data:

UserId  CreatedDate
4.........2017-01-11 01:40:19:077
4.........2017-01-11 01:40:19:437
4.........2017-01-11 01:40:20:077
4.........2017-01-11 01:50:19:077
4.........2017-01-11 02:40:19:077
4.........2017-01-11 02:40:19:437
4.........2017-01-11 02:40:20:077
4.........2017-01-11 02:40:20:437
4.........2017-01-11 02:40:21:077
4.........2017-01-11 02:40:22:077
4.........2017-01-11 02:40:23:077
4.........2017-01-11 03:15:19:077
4.........2017-01-11 03:40:19:077
4.........2017-01-11 04:40:19:077

The first three rows would be grouped together, and rows 5-11 would be grouped together (because they're all within 10 seconds of each other).

I tried something like this, but that only gives me rows in groupings of two. I'd like to know all rows within that 10-second range.

;WITH CTE AS
(
    SELECT UserId
        ,CreatedDate
        ,ISNULL(LAG(CreatedDate) OVER (Partition BY UserId ORDER BY CreatedDate), '1/1/2000') AS PriorCreatedDate
    FROM Foo
)
SELECT *
FROM CTE
WHERE DATEDIFF(SECOND,PriorCreatedDate,CreatedDate) <= 1
ORDER BY UserId, CreatedDate

Is this possible?

Upvotes: 1

Views: 720

Answers (2)

Yacoub Badran
Yacoub Badran

Reputation: 76

This way have more performance:

SELECT UserId, Date = Min( --Min or any value in the group give the same value
    case when DATEDIFF(SECOND, PreviousDate, creationDate) <= 10 
        then creationDate
        else PreviousDate
        end
)

FROM (
SELECT *,
PreviousDate = LAG(CreatedDate, 1, CreatedDate) OVER (Partition BY UserId ORDER BY CreatedDate desc), 
FROM CTE
)

GROUP BY UserId, case when DATEDIFF(SECOND, PreviousDate, creationDate) <= 10 
        then creationDate
        else PreviousDate
        end
ORDER BY UserId, Date

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81990

Perhaps something along these lines...

Example

Declare @YourTable Table ([UserId] int,[CreatedDate] datetime)
Insert Into @YourTable Values 
 (4,'2017-01-11 01:40:19:077')
,(4,'2017-01-11 01:40:19:437')
,(4,'2017-01-11 01:40:20:077')
,(4,'2017-01-11 01:50:19:077')
,(4,'2017-01-11 02:40:19:077')
,(4,'2017-01-11 02:40:19:437')
,(4,'2017-01-11 02:40:20:077')
,(4,'2017-01-11 02:40:20:437')
,(4,'2017-01-11 02:40:21:077')
,(4,'2017-01-11 02:40:22:077')
,(4,'2017-01-11 02:40:23:077')
,(4,'2017-01-11 03:15:19:077')
,(4,'2017-01-11 03:40:19:077')
,(4,'2017-01-11 04:40:19:077')

;with cte as (
    Select *
          ,Flg = case when datediff(SECOND,lag(CreatedDate,1,CreatedDate) over (Partition By UserID Order by CreatedDate),CreatedDate ) >=10 then 1 else 0 end
     From @YourTable
) 
Select UserID
      ,DateR1 = min(CreatedDate)
      ,DateR2 = max(CreatedDate)
      ,RecCnt = sum(1)
 From (Select *,Grp=sum(Flg) over (partition by UserID order by CreatedDate) From  cte ) A
 Group by UserID,Grp

Returns

UserID  DateR1                      DateR2                      RecCnt
4       2017-01-11 01:40:19.077     2017-01-11 01:40:20.077     3
4       2017-01-11 01:50:19.077     2017-01-11 01:50:19.077     1
4       2017-01-11 02:40:19.077     2017-01-11 02:40:23.077     7
4       2017-01-11 03:15:19.077     2017-01-11 03:15:19.077     1
4       2017-01-11 03:40:19.077     2017-01-11 03:40:19.077     1
4       2017-01-11 04:40:19.077     2017-01-11 04:40:19.077     1

EDIT - Requested Notes

If you were to execute

;with cte as (
    Select *
          ,Flg = case when datediff(SECOND,lag(CreatedDate,1,CreatedDate) over (Partition By UserID Order by CreatedDate),CreatedDate ) >=10 then 1 else 0 end
     From @YourTable
) 
Select *
      ,Grp=sum(Flg) over (partition by UserID order by CreatedDate) 
From  cte

The results would be:

enter image description here

Notice the Flg and Grp columns. The Grp column is essentially a running total of the Flg column.

Upvotes: 2

Related Questions