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.

I'm struggling to work out how the Start and End times shown in the expected results relate back to any of the datetime values in the sample data. Can you add more explanation please? Also, presumably Count = Rate (disparity between narrative and sample/expected) — Damien_The_Unbeliever, Feb 05 '21 at 14:23
`1/9/2022 10:00` and `1/9/2022 11:00` don't even show up in your sample data; where are they coming from in your expected results? — Thom A, Feb 05 '21 at 14:29
I just poached a slightly modified version of Lucero's answer here https://stackoverflow.com/questions/923295/how-can-i-truncate-a-datetime-in-sql-server — Error_2646, Feb 05 '21 at 14:34
Is it possible to have a row that, say, starts at 14:03 and ends at 15:37? How would that be handled? — Lasse V. Karlsen, Feb 05 '21 at 14:38
My apologies, experts. I was trying post a portion of the requirement to simplify, looks like that added the confusion. Full details have been updated, please let me know if any more clarifications required on the logic. — Manu, Feb 06 '21 at 15:23

score 1 · Answer 1 · edited Feb 05 '21 at 16:40

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

edited Feb 05 '21 at 16:40

Akif

7,098
7
27
53

answered Feb 05 '21 at 16:27

user15153489

11
2

1

Thank you for contributing an answer. Would you kindly edit your answer to to include an explanation of your code? That will help future readers better understand what is going on, and especially those members of the community who are new to the language and struggling to understand the concepts. – Jeremy Caney Feb 06 '21 at 00:57
1

The community encourages adding explanations alongisde code, rather than purely code-based answers (see [here](https://meta.stackoverflow.com/questions/300837/what-comment-should-i-add-to-code-only-answers)). – costaparas Feb 06 '21 at 03:05

Error_2646 · Answer 2 · 2021-02-05T14:43:29.530

Something like (I'm assuming you made a typo in the sample expected data and the starts/ends are meant to be the 0th minutes)

SELECT SUM(Rate),
       Trunc_Start,
       Trunc_End
  FROM (
    SELECT dateadd(hour, datediff(hour, 0, Start_Time), 0) AS Trunc_Start,
           dateadd(hour, datediff(hour, 0, ENd_Time) + 1, 0) AS Trunc_End,
           Rate
      FROM SOME_TABLE
  )
GROUP BY Trunc_Start,
         Trunc_end

score 0 · Answer 3 · answered Feb 05 '21 at 14:50

0

select sum(Rate),StartTime ,EndTime from table group by StartTime ,EndTime

answered Feb 05 '21 at 14:50

Sukdev Mandal

1

The community encourages adding explanations alongisde code, rather than purely code-based answers (see [here](https://meta.stackoverflow.com/questions/300837/what-comment-should-i-add-to-code-only-answers)). Also, please have a read of [this](https://stackoverflow.com/editing-help) help page about how to format code properly. – costaparas Feb 06 '21 at 03:05

SQL aggregate based on date range

Business logic

3 Answers3