Splitting Start Date Time End Date Time Into Hourly Duration Per Day

Question

I have a table like this:

Work Center Name  Start Date                    Stop Date
AA                2020-02-25 19:25:21.25212521  2020-02-25 20:02:44.244244
AA                2020-02-25 08:56:20.56205620  2020-02-25 09:16:33.16331633
AA                2020-02-25 14:32:04.324324    2020-02-25 14:34:38.34383438
AA                2020-02-25 15:21:54.21542154  2020-02-25 15:26:24.26242624
AA                2020-02-25 15:07:28.728728    2020-02-25 15:11:25.11251125
AA                2020-02-25 14:08:56.856856    2020-02-25 14:11:41.11411141
AA2               2020-06-09 19:34:11.34113411  2020-06-10 09:29:45.29452945

Essentially I have a start date and time and I would like to show a date and a duration in hours for each day.

Expected output:

Is this possible?

Thanks, Mike

Answer 1

I have come up with this ugly solution - keeping in mind that the duration of work can be more than a day. I have had to use a table valued function that returns the MINUTES (instead of hours from prev version of answer) between a start and end date time.

See the code below for the function (thanks for this answer: https://stackoverflow.com/a/27161879/65347)

go

create function dbo.minutes_between_start_end
(
@minDateTime DATETIME2
,@maxDateTime DATETIME2
)

returns @t table (Date_Value date, Hour_Value datetime2) 

As

BEGIN


DECLARE @hrsDiff INT;
SELECT @hrsDiff = DATEDIFF(MINUTE, @minDateTime, @maxDateTime);

WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), 
E4(N) AS (SELECT 1 FROM E2 a, E2 b), 
Tally(N) AS(SELECT row_number() over(order by (select null)) from E4) -- Numbered rrow

insert into @t(Date_Value, Hour_Value)
SELECT 
cast(@minDateTime as date)      as [Date_Value] 
,@minDateTime                   as [Hour_Value]

UNION ALL

SELECT 

cast(DATEADD(MINUTE, N, @minDateTime) as date)  as [Date_Value] 

, CASE 
    when DATEADD(MINUTE, N, @minDateTime) > @maxDateTime THEN   @maxDateTime
    else DATEADD(MINUTE, N, @minDateTime)
END as [Hour_Value]

FROM 

Tally
WHERE
    N <= @hrsDiff
;
return

END

GO 

With the function, I have cross-joined the the function output and multiple CTEs to come up with the output.

/* Test table and test data */
declare @test table
(
work_center varchar(10)
,start_date datetime
,end_date datetime
)

insert into @test(work_center, start_date, end_date)
select 'AA', '2020-02-25 19:25:21.25212521',  '2020-02-25 20:02:44.244244' union
select 'AA', '2020-02-25 08:56:20.56205620',  '2020-02-25 09:16:33.16331633' union
select 'AA', '2020-02-25 14:32:04.324324',  '2020-02-25 14:34:38.34383438' union
select 'AA', '2020-02-25 15:21:54.21542154',  '2020-02-25 15:26:24.26242624' union
select 'AA', '2020-02-25 15:07:28.728728',  '2020-02-25 15:11:25.11251125' union
select 'AA', '2020-02-25 14:08:56.856856',  '2020-02-25 14:11:41.11411141' union
select 'AA2','2020-06-09 19:34:11.34113411',  '2020-06-10 09:29:45.29452945' UNION
select 'aa3', '2020-06-09 19:34:11', '2020-06-11 09:29:45' -- case > 1 day

/* table a1 with rows for each hour between start and end time */
;with a1 as
(
select * 
from 
@test t

cross apply

dbo.minutes_between_start_end(start_date, end_date)
)
--select * from a1 
,a2 as
(
    select work_center, Date_Value, count(1) as total_minute_per_day 
    from a1 
    group by 
    work_center, Date_Value
)

select 
*
, cast(total_minute_per_day/60.00 as decimal(4,2)) total_hour_per_day  
from a2

The result returned is given below. Note that I have one entry spanning for more than 24 hrs.

Answer 2

You can use a Case to force the end of the current day, and a Union to include the records that end the following day:

select [Work Center] =  [Work Center Name], 
       StartDate = convert(date, [Start Date]),
       Duration = cast(sum(datediff(minute, [Start Date], (Case when convert(date, [Stop Date]) > convert(date, [Start Date]) then convert(date, [Stop Date]) --If record ends following day, calculate only until start of following day.
                                                        else [Stop Date] 
                                                    end) ) ) / 60.0 as decimal(5,1) )
from yourTable
group by [Work Center Name], convert(date, [Start Date])
Union
select [Work Center] =  [Work Center Name],
       StartDate = convert(date, [Stop Date]),
       Duration = cast(sum(datediff(minute, convert(date, [Stop Date]), [Stop Date]) ) / 60.0 as decimal(5,1) )                                     
from yourTable
where convert(date, [Stop Date]) > convert(date, [Start Date]) --Only records that end the following day
group by [Work Center Name], convert(date, [Stop Date])

Output:

Answer 3

I think you want:

select name, convert(date, startdate),
       sum(datediff(minute, startdate, enddate)) / 60.0 as hours
from t
group by name, convert(date, startdate);