How do I group records by date tolerance?

user_6109754 0
Viewed 66
DROP TABLE IF EXISTS  #Groups;

CREATE TABLE #Groups 
(
     [entity] nvarchar(30), 
     [workItem] nvarchar(255), 
     [CreatedDate] datetime
)

INSERT INTO #Groups ([entity], [workItem], [CreatedDate])
VALUES
( N'5002', N'AG', N'2020-09-04T13:24:00.823' ), 
( N'5002', N'AG', N'2020-09-04T13:23:05.103' ), 
( N'5002', N'AG', N'2020-09-04T14:23:05.103' ), 
( N'5002', N'SH', N'2020-09-04T13:26:42.367' ), 
( N'5002', N'SH', N'2020-09-04T13:27:17.25'  ), 
( N'5003', N'SH', N'2020-10-04T15:36:42.367' ), 
( N'5003', N'SH', N'2020-10-04T15:37:17.25'  );

Expected result:

entity  workItem    CreatedDate
--------------------------------------------
5002    AG          2020-09-04 13:24:00.823
5002    AG          2020-09-04 14:23:05.103
5002    SH          2020-09-04 13:27:17.250
5003    SH          2020-10-04 15:37:17.250

Basically, I need to group by entity, workitem and date with a one-minute tolerance.

Here is my attempt (though I didn't go too far):

SELECT
    t1.entity,
    t1.workItem,
    t1.CreatedDate,
    LAG(t1.CreatedDate, 1) OVER (PARTITION BY   t1.entity, t1.workItem ORDER BY t1.CreatedDate) AS CreateDate_new
FROM 
    #Groups t1
LEFT JOIN 
    #Groups t2 ON t2.entity = t1.entity
                AND t2.workItem = t1.workItem
                AND t2.CreatedDate >= t1.CreatedDate
                AND (DATEDIFF(MINUTE, t1.CreatedDate, t2.CreatedDate) < 1)
GROUP BY 
    t1.entity, t1.workItem, t1.CreatedDate
ORDER BY 
    t1.workItem
tsql sql-server
1 Answers

I think this is called a 'gaps and islands' problem and we have a fair few of these on SO.

The approach below is that to

  • find and group up all those within 1 minute of each other
  • then find the relevant values from within those groups.

Here is one approach to doing this

WITH Groups_Flagged AS
    (SELECT [Entity], [workItem], [CreatedDate],
            CASE WHEN DATEDIFF(second, LAG([CreatedDate], 1) OVER (PARTITION BY [Entity], [workItem] ORDER BY [CreatedDate]), [CreatedDate]) < 60 THEN 0 ELSE 1 END AS NewGrp_Flag
     FROM #Groups
    ),
Groups_Grouped AS
    (SELECT  [Entity], [workItem], [CreatedDate], SUM(NewGrp_Flag) OVER (ORDER BY [Entity], [workItem], [CreatedDate]) AS GrpNum
     FROM Groups_Flagged
     )
SELECT [Entity], [workItem], MAX([CreatedDate]) AS [CreatedDate]
FROM Groups_Grouped
GROUP BY [Entity], [workItem], [GrpNum]
ORDER BY [Entity], [workItem];

The Groups_Flagged CTE flags which records are 'new' e.g., not within 1 minute (60 seconds) of the previous record for that entity/workitem combination. It flags new records with '1', others '0'.

The Groups_Grouped CTE then uses a running total of those 1s and 0s to create group numbers for each one of those clusters.

The final select groups by Entity, WorkItem, and the new group numbers, and gets the maximum CreatedDate for those.

Note though that this treats a chain of activities as one group (e.g., if you have 5 createdates, all 50s apart, it counts as one). If you don't want this to occur, you will need to explicitly specify how to deal with these chains.

(PS thanks for having the script to create data - it makes answering these a whole lot easier!)

user_142674250
Related