T-SQL check if event has occurred continuously for 4 hours or more within 24 hours

Question

I have a table (event_tbl) of events that occurred. I need to find out for example if any of the events (Sales, Regcustomer and Transfer) happen continuously for 4 hours with 24 hours.

event_tbl:

empID	staffID	chartTime	Sales	RegCustomer	Transfer
T7458529	31509	2022-04-07 13:20:00.000	Yes	Yes	Yes
T7458529	31509	2022-04-07 14:00:00.000	Yes	Yes	Yes
T7458529	31509	2022-04-07 15:00:00.000	Yes	Yes	No
T7458529	31509	2022-04-07 16:00:00.000	Yes	Yes	Yes
T7458529	31509	2022-04-07 17:00:00.000	Yes	No	Yes
T7458529	31509	2022-04-07 18:00:00.000	Yes	Yes	Yes
T7458529	31509	2022-04-07 19:00:00.000	Yes	Yes	Yes

This is my desired result:

empID	staffID	chartTime	Sales	RegCustomer	Transfer
T7458529	31509	2022-04-07	Yes	Yes	No

So in this example, events Sales and RegCustomer got flagged as yes because they occurred 4 consecutive times within 24 hours. The Transfer event did not.

Don't even know where to begin as it seems to be complex due to how the events are being logged. Grateful if anyone could help.

Answer 1

Had to make a few assumptions, but I think this should get you close. Also not sure your expected results are correct. The Transfer event occurs 4 consecutive hours at hours 16-19

Track if Event Occurred 4 Consecutive Hours in a Single Day

DROP TABLE IF EXISTS #Event 
DROP Table IF EXISTS #RollingWindowCheck

CREATE TABLE #Event (ID INT IDENTITY(1,1),empID CHAR(8),staffID INT,chartTime DATETIME,Sales CHAR(3),RegCustomer CHAR(3),[Transfer] CHAR(3))

INSERT INTO #Event VALUES
('T7458529',31509,'2022-04-07 13:20:00.000','Yes','Yes','Yes')
,('T7458529',31509,'2022-04-07 14:00:00.000','Yes','Yes','Yes')
,('T7458529',31509,'2022-04-07 15:00:00.000','Yes','Yes','No')
,('T7458529',31509,'2022-04-07 16:00:00.000','Yes','Yes','Yes')
,('T7458529',31509,'2022-04-07 17:00:00.000','Yes','No' ,'Yes')
,('T7458529',31509,'2022-04-07 18:00:00.000','Yes','Yes','Yes')
,('T7458529',31509,'2022-04-07 19:00:00.000','Yes','Yes','Yes');

WITH cte_EventDate AS ( 
    SELECT *,ChartDay = CAST(ChartTime AS DATE)
    FROM #Event
)

SELECT *
INTO #RollingWindowCheck
FROM cte_EventDate AS A
OUTER APPLY (
    /*Count distinct hours, since where clause limits it to 4 hours, if 4 distinct hours, then you know they are consecutive.
    Each column only counts that hour though if the Event = 'Yes'*/
    SELECT SalesDistinctHrCnt       = COUNT(DISTINCT CASE WHEN Sales        = 'Yes' THEN DATEPART(HOUR,DTA.ChartTime) END)
        ,RegCustomerDistinctHrCnt   = COUNT(DISTINCT CASE WHEN RegCustomer  = 'Yes' THEN DATEPART(HOUR,DTA.ChartTime) END)
        ,TransferDistinctHrCnt      = COUNT(DISTINCT CASE WHEN [Transfer]   = 'Yes' THEN DATEPART(HOUR,DTA.ChartTime) END)
    FROM cte_EventDate AS DTA
    WHERE A.empID = DTA.empID
    AND A.ChartDay = DTA.ChartDay /*Only match to same day*/
    AND A.ChartTime BETWEEN DATEADD(HOUR,-4,DTA.ChartTime) AND DTA.ChartTime /*Match to any times within 4 hours*/
    AND A.ID <> DTA.ID /*Don't match record to itself. Assuming you have a primary key like ID. Alternatively could use A.ChartTime <> DTA.ChartTime but not ideal*/
) AS B

SELECT 
    empID
    ,staffID
    ,ChartDay
    /*Basically if there is a group of 4, you know there were 4 consecutive hours where that event occurred. 
    If not, will be NULL so use ISNULL to return 'No'*/
    ,Sales          = ISNULL(MAX(CASE WHEN SalesDistinctHrCnt = 4 THEN 'Yes' END),'No')
    ,RegCustomer    = ISNULL(MAX(CASE WHEN RegCustomerDistinctHrCnt = 4 THEN 'Yes' END),'No')
    ,[Transfer]     = ISNULL(MAX(CASE WHEN TransferDistinctHrCnt = 4 THEN 'Yes' END),'No')
FROM #RollingWindowCheck
GROUP BY empID,staffID,ChartDay