question

aRookieBIdev avatar image
aRookieBIdev asked

SQL Query to group relatively closer dates

I am having a tough time with grouping records based on the closer dates and one other condition

In the data shown in the picture ,

I would like to group ProcessId 3 , 4 AND 5 together as one group based on the following condition

  • Process 4 started with in 2 days after the process 3 ended.
  • Process 5 started with in 2 days after the process 4 ended.
  • All process has less than 10 steps. (Any process more than 10 steps should not be grouped)

Any process more than 10 steps will break the continuity of the group. There should be new group after that

Thanks you and appreciate any help

CREATE TABLE [dbo].[process] 
  ( 
     [client] [VARCHAR](50) NULL,[processid] [VARCHAR](50) NULL, 
     [startdate] [DATETIME] NULL,[enddate] [DATETIME] NULL,[steps] [INT] NULL, 
     [groupprocessid] [INT] NULL 
  ); 


INSERT INTO [dbo].[process] 
            ([client],[processid],[startdate],[enddate],[steps],[groupprocessid] 
) 
VALUES      ('A',1,'2018-05-01 15:48:21.837','2018-05-02 15:48:21.837',5,NULL) 

INSERT INTO [dbo].[process] 
            ([client],[processid],[startdate],[enddate],[steps],[groupprocessid] 
) 
VALUES      ('A',2,'2018-05-03 15:48:21.837','2018-05-04 15:48:21.837',11,NULL) 

INSERT INTO [dbo].[process] 
            ([client],[processid],[startdate],[enddate],[steps],[groupprocessid] 
) 
VALUES      ('A',3,'2018-05-05 15:48:21.837','2018-05-06 15:48:21.837',3,NULL) 

INSERT INTO [dbo].[process] 
            ([client],[processid],[startdate],[enddate],[steps],[groupprocessid] 
) 
VALUES      ('A',4,'2018-05-07 15:48:21.837','2018-05-08 15:48:21.837',9,NULL) 

INSERT INTO [dbo].[process] 
            ([client],[processid],[startdate],[enddate],[steps],[groupprocessid] 
) 
VALUES      ('A',5,'2018-05-09 15:48:21.837','2018-05-10 15:48:21.837',7,NULL) 

sql-server-2012datetimegroupinglead
untitled.png (7.6 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Just so I understand the requirement here:

For processes with the same Client, a process should be group together with the previous process ("previous" based on sortorder startdate ascending) if the process and the previous process has 10 steps or less and if the process' startdate is less than 48 hours later than the previous process' enddate?

And what should the GroupProcessID be? Just a counter?

0 Likes 0 ·

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

Assuming requirements are as in my comment, this should work.

WITH CTE AS(
 SELECT client,processid,startdate,enddate,steps, 
 CASE 
  WHEN steps<=10 
    AND LAG(steps,1,100) OVER(PARTITION BY client ORDER BY startdate)<=10 
    AND DATEDIFF(HOUR,LAG(enddate,1,'1900-01-01') OVER(PARTITION BY client ORDER BY startdate),startdate)<48
  THEN 0 
  ELSE 1
 END AS NewGroup
 FROM process 
)
SELECT 
  client,
  processid,
  startdate,
  enddate,
  steps,
  SUM(NewGroup) OVER(PARTITION BY client ORDER BY startdate ROWS UNBOUNDED PRECEDING) AS GroupProcessID 
FROM CTE 
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.