Calculate number of groups and group size with multiple criteria

Question

My data looks like this:

I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table

GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end 

and in the second Temp Table, I have

select
,GrpSize=sum(grpsize)
,NoofGrp=count(distinct grpsize)
From Temp1

The issue is for the date of 5/26, the begtime and endtime are not consistent. In Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I want that client to be in the second group (grp2) My partition over creates 4 groups rather than two. Any suggestions?

Answer 1

with cte1 as (
select date1,id,ROW_NUMBER() over (order by date1) as rn
from #a
),cte2 as (
select top 1 date1,id,rn,cast('grp1' as varchar(10)) as grp ,i = 1
from cte1
union all
select b.date1,b.id,b.rn,cast('grp' + cast((case when a.id = 10 then i+1 else i end) as varchar(10)) as varchar(10)), (case when a.id = 10 then i+1 else i end)
from cte2 as a
inner join cte1 as b
on b.rn = a.rn + 1
)
select *,(case when id = 10 then 'grp' + cast(COUNT(grp) over (partition by grp) as varchar(10)) else '' end)
 from cte2