-1

How do I convert a date range so each day is 1 row with the start and end time for that day?

I looked at this post about date ranges to row - but this is a different problem. The other solution linked above does not give the time from start to finish for each day - and thus does not allow for duty factor or utilization calculations, and or the build of a Gantt chart.

We would have an ID field, a Start Date, and an End Date as our base table. We want to convert this to contain the ID Field per day with how much time was consumed in that range.

This is very useful when converting a start and end dates to daily duty factor and a host of other needs like equipment utilization.

Undead-Earth .com
  • 71
  • 9
  • Tag the dbms you're using, you've already got one product specific answer. – jarlh May 22 '17 at 12:15
  • Add some sample table data, and the expected result. Also show us your current query attempt. – jarlh May 22 '17 at 12:16
  • Possible duplicate of [How to generate a range of dates in SQL Server](https://stackoverflow.com/questions/11141507/how-to-generate-a-range-of-dates-in-sql-server) – McNets May 22 '17 at 12:34

1 Answers1

0

I had a lot of help from this community figuring this out. I wanted to put the final SQL script here for others to use as well.

WITH cte ([VID],[StartTime],[EndTime])  AS 
    ( SELECT   tbl.[ID] as 'VID'
              ,CONVERT(VARCHAR(19), tbl.[StartDT], 120) AS 'StartTime'
              ,CASE 
                 WHEN tbl.[EndDT] <= CONVERT(VARCHAR(11), tbl.[StartDT]+1, 120) + '00:00:00' THEN CONVERT(VARCHAR(19), tbl.[EndDT], 120)
                 ELSE CONVERT(VARCHAR(11), tbl.[StartDT]+1, 120) + '00:00:00'
               END as 'EndTime'
       FROM [SourceTable] as tbl
       WHERE DATEDIFF(DAY,tbl.[StartDT],tbl.[EndDT] )<=365

       UNION ALL

       SELECT  tbl.[ID] as 'VID'
              ,CONVERT(VARCHAR(11), DATEADD(DAY, 1, cte.[StartTime]), 120) + '00:00:00' AS 'StartTime'
              ,CASE
                 WHEN CONVERT(VARCHAR(19), tbl.[EndDT], 120) < CONVERT(VARCHAR(11), DATEADD(DAY, 2, cte.[StartTime]), 120) + '00:00:00'
                 THEN CONVERT(VARCHAR(19), tbl.[EndDT], 120)
                 ELSE CONVERT(VARCHAR(11), DATEADD(DAY, 2, cte.[StartTime]), 120) + '00:00:00'
               END AS 'EndTime'
        FROM cte
           INNER JOIN [SourceTable] as tbl
               ON cte.VID = tbl.ID 
        WHERE CONVERT(VARCHAR(11), cte.[StartTime], 120) < CONVERT(VARCHAR(11), tbl.[EndDT], 120))

 SELECT VID AS ID
       ,[StartTime]
       ,[EndTime]
       ,DateDiff (second,[StartTime],[EndTime]) / 3600  As 'Hours'
       ,DateDiff (second,[StartTime],[EndTime])/60 % 60 as 'Minutes'
       ,((DateDiff (Second,[StartTime],[EndTime]) / 3600)*60)+(DateDiff (second,starttime,endtime)/60 % 60) as 'Total Minutes'
       ,DATEPART(week,[StartTime]) AS weeknum
       ,MONTH([StartTime]) AS MonthNumber
       ,DATENAME(month,[StartTime]) AS MonthName

  FROM cte order by Id, [StartTime]
  option (maxrecursion 0);
Undead-Earth .com
  • 71
  • 9