CODEWITHSUNDEEP
sqlsql-server
Diogo Santos
Diogo Santos

Reputation: 830

Count hours within intervals grouped by day

I have a table tbl with the following sample code:

declare @tbl table
(
Date1 datetime,
Date2 datetime
)

insert into @tbl
values
('2020-06-03 11:00','2020-06-03 14:00'),
('2020-06-03 19:00','2020-06-04 01:00'),
('2020-06-04 11:00','2020-06-04 14:00')

select * from @tbl

I want to output the number of hours that where spent between the two columns.

Sample desired output:

Day        | Hours
2020-06-03 | 8
2020-06-04 | 4

I tried doing:

select sum(datediff(hour,Date1,Date2)) from @tbl
group by cast(Date1 as date)

But this doesn't have in mind the interval that crossed the two days, outputting 9 and 3.

Any ideas on this?

Thanks!

Upvotes: 0

Views: 44

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

For the generic solution, you can use a recursive CTE to split the time spans into separate days and then aggregate:

with cte as (
      select date1, date1 as date2,
             date2 as enddate, 1 as lev
      from t
      union all
      select date2,
             (case when datediff(day, date1, enddate) = 0 then enddate
                   else dateadd(day, 1, datefromparts(year(date1), month(date1), day(date1)))
              end) as date2,
             enddate, 1 + lev
      from cte
      where date1 < enddate 
     )
select convert(date, date1), sum(datediff(hour, date1, date2))
from cte
group by convert(date, date1);

Here is a db<>fiddle.

Upvotes: 1

Thom A
Thom A

Reputation: 95950

I'm ripping this answer from another, with a slight difference to aggregate per day instead.

You need to split your values into each day, and then you can SUM per day:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP(SELECT MAX(DATEDIFF(DAY, Date1, Date2)+1) FROM @tbl)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1, N N2, N N3), --1000 days enough?
Dates AS(
    SELECT DATEADD(DAY, T.I,CONVERT(date,YT.Date1)) AS [Date],
           CASE WHEN T.I = 0 THEN YT.Date1 ELSE DATEADD(DAY, T.I,CONVERT(date,YT.Date1)) END AS StartingDateTime,
           CASE WHEN LEAD(T.I) OVER (PARTITION BY YT.Date1 ORDER BY T.I) IS NULL THEN YT.Date2 ELSE DATEADD(DAY, T.I+1,CONVERT(date,YT.Date1)) END AS EndingDateTime
    FROM Tally T
         JOIN @tbl YT ON T.I <= DATEDIFF(DAY, YT.Date1, YT.Date2))
SELECT D.[Date],
       SUM(DATEDIFF(HOUR,D.StartingDateTime,D.EndingDateTime)) AS [Hours]
FROM Dates D
GROUP BY D.[Date];

Upvotes: 0

Related Questions