SQL Query to group relatively closer dates

Question

question

aRookieBIdev asked May 16, '18

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-2012 datetime grouping lead

untitled.png (7.6 KiB)

1

10 |1200

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

Magnus Ahlkvist commented · May 17, 2018 at 04:54 AM

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 ·

Answer 1 · 2018-05-17T05:03:42Z

Magnus Ahlkvist answered May 17, '18

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

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

question