How to group rows by their DATEDIFF?
I hope you can help me.
I need to display the records in HH_Solution_Audit table -- if 2 or more
staffs enter the room within 10 minutes. Here are the requirements:
Display only the events that have a timestamp (LAST_UPDATED) interval of
less than or equal to 10 minutes. Therefore, I must compare the current
row to the next row and previous row to check if their DATEDIFF is less
than or equal to 10 minutes. I'm done with this part.
Show only the records if the number of distinct STAFF_GUID inside the room
for less than or equal to 10 minutes is at least 2.
HH_Solution_Audit Table Details:
ID - PK
STAFF_GUID - staff id
LAST_UPDATED - datetime when a staff enters a room
Here's what I got so far. This satisfies requirement # 1 only.
DECLARE @numOfPeople INT = 2,
--minimum number of people that must be inside
--the room for @lengthOfStay minutes
@lengthOfStay INT = 10,
--number of minutes of stay
@dateFrom DATETIME = '04/25/2013 00:00',
@dateTo DATETIME = '04/25/2013 23:59';
WITH cteSource AS
(
SELECT ID, STAFF_GUID, LAST_UPDATED,
ROW_NUMBER() OVER (ORDER BY LAST_UPDATED) AS row_num
FROM HH_SOLUTION_AUDIT
WHERE LAST_UPDATED >= @dateFrom AND LAST_UPDATED <= @dateTo
)
SELECT [current].ID, [current].STAFF_GUID, [current].LAST_UPDATED
FROM
cteSource AS [current]
LEFT OUTER JOIN
cteSource AS [previous] ON [current].row_num = [previous].row_num
+ 1
LEFT OUTER JOIN
cteSource AS [next] ON [current].row_num = [next].row_num - 1
WHERE
DATEDIFF(MINUTE, [previous].LAST_UPDATED, [current].LAST_UPDATED)
<= @lengthOfStay
OR
DATEDIFF(MINUTE, [current].LAST_UPDATED, [next].LAST_UPDATED)
<= @lengthOfStay
ORDER BY [current].ID, [current].LAST_UPDATED
No comments:
Post a Comment