CODEWITHSUNDEEP
sql-server-2012
user3606175
user3606175

Reputation: 2193

Split hours between dates based on date range

Split hours spanning between multiple days

I have data like this.

StartDate       EndDate
2015-10-05 20:00:00.000 2015-10-06 12:00:00.000

I need this data to be split by date like

2015-10-05 240 (4 hours)
2015-10-06 720 (12 hours)

I can get the first start date split like this

        select (StartDate as date) as StartDate,
            DATEDIFF(minute, StartDate, dateadd(day, 1, Cast(StartDate as date))) as diff           
        from t

which gives me 

2015-10-05 240

And get the end date's data like

        select 
            Cast(EndDate as date) as StartDate,
            DATEDIFF(minute, Cast(EndDate as date), EndDate) as diff                
        from t


2015-10-06 720

But I am not sure how to do it all in one query. Besides, the time diff betweens start and end can be more than one day diff like this

StartDate       EndDate
2015-10-05 20:00:00.000 2015-10-08 12:00:00.000

for which I need

2015-10-05 240
2015-10-06 1440
2015-10-07 1440
2015-10-06 720

Thanks for looking

Upvotes: 0

Views: 172

Answers (2)

shurik
shurik

Reputation: 805

This should cover when start and end are on the same date and no limit on days

EDIT: fixed issue with incorrect calculation for the end date

declare @StartDate datetime, @EndDate datetime
set @StartDate = '2015-10-05 20:00'
set @EndDate = '2015-10-05 21:00'

;WITH cte AS
(
    SELECT cast(@StartDAte as date) StartDate, 
           cast(dateadd(day, 1, @StartDAte) as date) EndDate
    UNION ALL
    SELECT DATEADD(day, 1, StartDate) StartDate, 
           DATEADD(day, 2, StartDate) EndOfDate
    FROM   cte
    WHERE  DATEADD(day, 1, StartDate) <= @EndDate
)
select StartDate,
    case 
        when cast(@StartDate as date) = cast(@EndDate as date) then datediff(minute, @StartDate, @EndDate ) 
        when StartDate = cast(@StartDate as date) then datediff(minute, @StartDate, cast(EndDate as datetime)) 
        when StartDate = cast(@EndDate as date) then datediff(minute, cast(StartDate as datetime), @EndDate) 
        else 1440 end
from cte

Upvotes: 2

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this:

Remark: If the full intervall can be more than 10 days just add some more values to the tally table. Attention: This solution does not yet cover the situation, when start and end is on the same day...

DECLARE @d1 DATETIME={ts'2015-10-05 08:00:00'};
DECLARE @d2 DATETIME={ts'2015-10-07 12:00:00'};

WITH SomePreCalculations AS
(
    SELECT @d1 AS D1
          ,@d2 AS D2
          ,CAST(@d1 AS DATE) AS StartDate  
          ,DATEADD(DAY,1,CAST(@d1 AS DATE)) AS FirstMidnight
          ,CAST(@d2 AS DATE) AS LastMidnight  
)
,Differences AS
(
    SELECT *
          ,DATEDIFF(MINUTE,D1,FirstMidnight) AS TilMidnight
          ,DATEDIFF(MINUTE,LastMidnight,D2) AS FromMidnight
    FROM SomePreCalculations
)
,TallyTable AS
(
    SELECT RowInx FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS x(RowInx)
)
SELECT CAST(Date AS DATE),Minutes FROM
(
      SELECT 0 AS Inx, D1 AS Date, TilMidnight AS Minutes 
      FROM Differences  
UNION SELECT RowInx,(SELECT DATEADD(DAY,RowInx,(SELECT StartDate FROM SomePreCalculations))),1440
      FROM TallyTable   
      WHERE DATEADD(DAY,RowInx,(SELECT StartDate FROM SomePreCalculations))<(SELECT LastMidnight FROM SomePreCalculations)
UNION SELECT 99 AS Inx, D2, FromMidnight  
      FROM Differences  
) AS tbl
ORDER BY tbl.Inx

Upvotes: 1

Related Questions