SQL query to group with multiple instance of same group.

Question

Would anyone know of a method to achieve the following:

Sample transaction data as follows:

Status |StartDateTime |EndDateTime |UserID

Started |2021/01/09 09:23:16 |2021/01/10 11:30:00 |1
Started |2021/01/10 11:30:00 |2021/01/10 12:45:15 |5
Started |2021/01/10 12:45:15 |2021/01/12 04:10:43 |4

Paused |2021/01/12 04:10:43 |2021/01/14 10:03:00 |1

Started |2021/01/14 10:03:00 |2021/01/14 10:33:11 |5
Started |2021/01/14 10:33:11 |2021/01/14 14:47:16 |2

Progress |2021/01/14 14:47:16 |2021/01/14 14:54:41 |7
Progress |2021/01/14 14:54:41 |2021/01/18 11:29:32 |5

Desired summary, where the "Status" start and end times are the min/max of the relative cluster of entries:

Status |StartDateTime |EndDateTime

Started |2021/01/09 09:23:16 |2021/01/12 04:10:43
Paused |2021/01/12 04:10:43 |2021/01/14 10:03:00
Started |2021/01/14 10:03:00 |2021/01/14 14:47:16
Progress |2021/01/14 14:47:16 |2021/01/18 11:29:32

Thanks

Answer 1

Check this query:

;
with Q as
(
   select 
      *, 
      row_number() over (order by StartDateTime) -
         dense_rank() over (partition by Status order by StartDateTime) as gn
   from MyTransactionTable
)
select 
   Status, 
   min(StartDateTime) as StartDateTime, 
   max(EndDateTime) as EndDateTime
from Q
group by Status, gn
order by StartDateTime

Answer 2

Hi @Scott Kent-Collins ，

Welcome to Microsoft Q&A!

Please refer below:

create table Tablesco  
(Status varchar(10),  
StartDateTime datetime,  
EndDateTime datetime,  
UserID int)  
  
insert into Tablesco values  
('Started','2021/01/09 09:23:16' ,'2021/01/10 11:30:00',1),  
('Started ','2021/01/10 11:30:00 ','2021/01/10 12:45:15 ',5),  
('Started ','2021/01/10 12:45:15 ','2021/01/12 04:10:43 ',4),  
('Paused ','2021/01/12 04:10:43 ','2021/01/14 10:03:00 ',1),  
('Started ','2021/01/14 10:03:00 ','2021/01/14 10:33:11 ',5),  
('Started ','2021/01/14 10:33:11 ','2021/01/14 14:47:16 ',2),  
('Progress ','2021/01/14 14:47:16 ','2021/01/14 14:54:41 ',7),  
('Progress ','2021/01/14 14:54:41 ','2021/01/18 11:29:32 ',5)  

;with cte as (  
 select *, Lead(StartDateTime) OVER (partition by Status ORDER BY StartDateTime) AS Next  
  from Tablesco)  
 ,cte1 as (  
     select StartDateTime, EndDateTime,Status  
     from cte  
     union all  
     select  cte1.StartDateTime, t.EndDateTime, t.Status  
     from cte1  
     join cte t on  cte1.EndDateTime = t.StartDateTime and cte1.Status=t.Status   
 )  
 , cte2 as (  
     select *, rn = row_number() over (partition by Status,EndDateTime order by StartDateTime)  
     from cte1  
 )  
 select Status, min(StartDateTime) StartDateTime, max(EndDateTime) EndDateTime  
 from cte2  
 where rn=1  
 group by Status,StartDateTime  

Output:

Status StartDateTime EndDateTime  
Started 2021-01-09 09:23:16.000 2021-01-12 04:10:43.000  
Paused  2021-01-12 04:10:43.000 2021-01-14 10:03:00.000  
Started  2021-01-14 10:03:00.000 2021-01-14 14:47:16.000  
Progress  2021-01-14 14:47:16.000 2021-01-18 11:29:32.000  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.