sql server - SQL aggregate based on date range

Question

I'm having following data in a MSSQL table. The requirement is to group the records for users which falls under same/end time duration, and sum up the Rate field.

Is there any way to achieve this via query on-the-fly?

Row Data
-----------------------------------------------------
RawId   Start Time      End Time        User    Rate
1       1/9/2021 14:29  1/9/2021 14:40  User-1  10
2       1/9/2021 10:37  1/9/2021 14:00  User-2  20
3       1/9/2021 14:03  1/9/2021 14:59  User-2  30
4       1/9/2021 8:51   1/9/2021 14:39  User-1  40
5       1/9/2021 14:02  1/9/2021 14:59  User-2  50

Expected Output
-----------------------------------------------------
ProID   Start Time      End Time        User    RateTotal
xx1     1/9/2021 14:29  1/9/2021 14:40  User-1  50          
xx2     1/9/2021 14:02  1/9/2021 14:59  User-2  80
xx3     1/9/2021 10:37  1/9/2021 14:00  User-2  20

Business logic

ProID xx1: RawID 1 & 4, belong to User-1 and RawID 1 start & end time (14:29-14:40) falls within RawID 4 (08:51-14:39). In this case rates have to be added up and show only one record.

ProID xx2: RawID 3 & 5, belong to User-2 and RawID 3 start & end time (14:03-14:59) falls within RawID 5 (14:02-14:59). In this case rates have to be added up and show only one record.

ProID xx3: RawID 2 also belongs to User-2 but start & end time (10:37-14:00) doesnt fall within other User-2 records. Hence this will be considered as separate row.

question from:https://stackoverflow.com/questions/66064930/sql-aggregate-based-on-date-range

Answer 1

with cte as

(

select Rate as Rate,dateadd(hour,datediff(HOUR,0,StartTime),0) as starttime, 
dateadd(HOUR,DATEDIFF(hour,0,endtime),0)  as EndTime 
from Row_Data

)

select sum(rate) as Rate,StartTime,Endtime from cte

group by StartTime,EndTime 

order by starttime desc