Log in  ::  Register  ::  Not logged in

## Determine time elapased by hour

 Author Message m.dunster SSC Journeyman Group: General Forum Members Points: 92 Visits: 266 Hi,Wondering if anyone has an elegant solution to finding out how much time has elapsed for a specific hour.`declare @t as table (ptr int identity, START time, ENDD time)insert into @t (START, ENDD)SELECT '09:30', '10:30'union allSELECT '10:00', '11:00'union allSELECT '10:30', '11:30'`Taking the example table above, I am looking to output the amount of time elapsed for each hour block. So:09 - 0.510 - 211 - 0.5I can think of complicated and ugly ways using cursors and many lines of code, but I thought someone might have a more user friendly solution!Many thanks for your time.Matt Sean Lange SSCoach Group: General Forum Members Points: 16564 Visits: 17015 m.dunster (10/30/2013)Hi,Wondering if anyone has an elegant solution to finding out how much time has elapsed for a specific hour.`declare @t as table (ptr int identity, START time, ENDD time)insert into @t (START, ENDD)SELECT '09:30', '10:30'union allSELECT '10:00', '11:00'union allSELECT '10:30', '11:30'`Taking the example table above, I am looking to output the amount of time elapsed for each hour block. So:09 - 0.510 - 211 - 0.5I can think of complicated and ugly ways using cursors and many lines of code, but I thought someone might have a more user friendly solution!Many thanks for your time.MattThank you for providing ddl and sample data. I am confused by your expected output. Can you try to explain what you want? The business rules for the output is lost on me. _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2) m.dunster SSC Journeyman Group: General Forum Members Points: 92 Visits: 266 Hi,To explain further. Let us assume that table @t represents light bulbs being switched on and off. So the first light bulb is switched on at 9.30am and off at 10.30am, the second on at 10.00am and off at 11.00am, the third on at 10.30 am and off at 11.30am.I need to know how long, within hour blocks, the total time expended for the sum of all light bulbs being on.So in our example:Between 9am to 10am, light bulb 1 was on for 0.5 hours, light bulb 2&3 were both off, so a total of half an hour (0.5)Between 10am to 11am, light bulb 1 was on for 0.5 hours, light bulb 2 for an hour, and light bulb 3 on for 0.5 hours, so a total of 2 hours (2.0)Between 11.00am to 12.00pm, light bulb 1 was off, light bulb 2 was off, hour, and light bulb 3 on for 0.5 hours, so a total of half an hour (0.5) Mark Cowne SSCrazy Group: General Forum Members Points: 2091 Visits: 22794 Not quite a complete solution, it doesn't handle spanning midnight`WITH Hours(hrStart,hrEnd) AS (SELECT CAST(hrStart AS TIME),CAST(hrEnd AS TIME)FROM ( VALUES ('00:00','01:00'),('01:00','02:00'),('02:00','03:00'),('03:00','04:00'), ('04:00','05:00'),('05:00','06:00'),('06:00','07:00'),('07:00','08:00'), ('08:00','09:00'),('09:00','10:00'),('10:00','11:00'),('11:00','12:00'), ('12:00','13:00'),('13:00','14:00'),('14:00','15:00'),('15:00','16:00'), ('16:00','17:00'),('17:00','18:00'),('18:00','19:00'),('19:00','20:00'), ('20:00','21:00'),('21:00','22:00'),('22:00','23:00'),('23:00','00:00')) h(hrStart,hrEnd))SELECT h.hrStart AS [Hour], SUM(DATEDIFF(minute,CASE WHEN t.START < h.hrStart THEN h.hrStart ELSE t.START END, CASE WHEN t.ENDD > h.hrEnd THEN h.hrEnd ELSE t.ENDD END)) AS minutes FROM Hours hINNER JOIN @t t ON t.ENDD > h.hrStart AND t.START < h.hrEndGROUP BY h.hrStartORDER BY h.hrStart;` ____________________________________________________Deja View - The strange feeling that somewhere, sometime you've optimised this query beforeHow to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537 m.dunster SSC Journeyman Group: General Forum Members Points: 92 Visits: 266 Thanks Mark. I'll give it a go. The midnight issue shouldn't be a problem. dwain.c SSCarpal Tunnel Group: General Forum Members Points: 4253 Visits: 6431 Another take (also not handling crossing midnight), with some sample data added.`declare @t as table (ptr int identity, START time, ENDD time)insert into @t (START, ENDD)SELECT '09:30', '10:30' union allSELECT '10:00', '11:00' union allSELECT '10:30', '11:30' union allSELECT '09:30', '11:30' union allSELECT '11:00', '11:30';WITH Tally (n) AS( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM (VALUES (0),(0),(0),(0),(0),(0)) a(a) CROSS JOIN (VALUES (0),(0),(0),(0)) b(b))SELECT Hour=RIGHT(100+d.n-1,2) + ':00' ,LightBulbsShining=SUM( CASE WHEN StartHr=c.n AND EndHr=c.n THEN DATEDIFF(minute, START, ENDD)/60. WHEN StartHr=c.n AND EndHr<>c.n THEN DATEDIFF(minute, START, DATEADD(hour, 1, hr))/60. ELSE DATEDIFF(minute, hr, ENDD)/60. END) FROM @t aCROSS APPLY( SELECT StartHr=DATEPART(hour, START), EndHr=DATEPART(hour, ENDD)) bCROSS APPLY( SELECT n, hr=RIGHT(100+n,2) + ':00' FROM Tally WHERE n BETWEEN StartHr AND EndHr) c RIGHT JOIN Tally d ON d.n-1 = c.nGROUP BY RIGHT(100+d.n-1,2) + ':00';` My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables m.dunster SSC Journeyman Group: General Forum Members Points: 92 Visits: 266 Hi Mark,Your solution is good, but it has a limitation in that if a period start and end more than one hour apart, it doesn't get calculated.Dwain: Yours, I can't really understand (my limitation), but there is something wrong, if you alter the first end time to 13:30, then it counts all the extra hours in the 10:00 slot.Thanks for your efforts and help. I'll have to keep going until I beat it.Matt brad.mason5 SSCommitted Group: General Forum Members Points: 1521 Visits: 2008 Here is my shot at this using Mark-101232 derived table for times.`--use any database which has this numbers table. declare @t as table (ptr int identity, START time, ENDD time) insert into @t (START, ENDD)SELECT '09:30', '10:00'union allSELECT '10:00', '11:00'union allSELECT '10:30', '11:30'union allSELECT '13:30', '15:30'union allSELECT '18:30', '19:00'union allSELECT '10:00', '10:30'union allSELECT '03:00', '08:30'union allSELECT '19:00', '19:45'--uses number table--http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx/**************************************Most of this code is because of expanding the times in the start and end. If times span more than1 hour this will generate a new record for this.*******************************************/IF OBJECT_ID('tempdb..#LightHour','u') IS NOT NULL DROP TABLE #LightHour;WITH ExpandHoursAS(SELECT t.ptr ,e.RowNum ,StartTime = CAST(e.StartTime AS TIME) ,EndTime = CAST(e.EndTime AS TIME) ,OriginalStartTime = t.START ,OriginalEndTime = t.ENDDFROM @t tCROSS APPLY ( SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY t.ptr ORDER BY prev.Number) ,DATEADD(hh, prev.Number, dateadd(hour, datediff(hour, 0, t.START), 0)) AS StartTime ,DATEADD(hh, curr.Number, dateadd(hour, datediff(hour, 0, t.START), 0)) AS EndTime FROM dbo.Numbers curr JOIN dbo.Numbers prev ON curr.Number = prev.Number + 1 WHERE curr.Number <= DATEDIFF(hh, t.START, t.ENDD) ) e),FinalAS(SELECT ptr ,StartTime = CASE WHEN StartTime < OriginalStartTime THEN OriginalStartTime ELSE StartTime END ,EndTime = CASE WHEN OriginalEndTime < EndTime THEN OriginalEndTime ELSE EndTime ENDFROM ExpandHoursUNIONSELECT ptr ,StartTime = (SELECT MAX(c.EndTime) FROM ExpandHours c WHERE i.ptr = c.ptr) ,EndTime = OriginalEndTimeFROM ExpandHours i)SELECT ptr ,Start = StartTime ,Endd = EndTimeINTO #LightHourFROM FinalWHERE StartTime <> EndTimeUNION--if under 1 hour then include hereSELECT *FROM @tWHERE DATEDIFF(mi, START, ENDD) < 60IF OBJECT_ID('tempdb..#Final','u') IS NOT NULL DROP TABLE #FinalSELECT LighHour =hrStart ,TotalTimeOn_MI = SUM(CASE WHEN t.Start >= hrStart AND t.Start < hrEnd THEN DATEDIFF(mi, t.Start, t.Endd) ELSE 0 END) --,ptrINTO #FinalFROM #LightHour t CROSS APPLY (SELECT hrStart = CAST(hrStart AS TIME) ,hrEnd = CAST(hrEnd AS TIME)FROM ( VALUES ('00:00','01:00'),('01:00','02:00'),('02:00','03:00'),('03:00','04:00'), ('04:00','05:00'),('05:00','06:00'),('06:00','07:00'),('07:00','08:00'), ('08:00','09:00'),('09:00','10:00'),('10:00','11:00'),('11:00','12:00'), ('12:00','13:00'),('13:00','14:00'),('14:00','15:00'),('15:00','16:00'), ('16:00','17:00'),('17:00','18:00'),('18:00','19:00'),('19:00','20:00'), ('20:00','21:00'),('21:00','22:00'),('22:00','23:00'),('23:00','24:00')) h(hrStart,hrEnd) ) hGROUP BY h.hrStart -- ,ptrSELECT *FROM #FinalWHERE TotalTimeOn_MI > 0` mister.magoo SSCrazy Group: General Forum Members Points: 2268 Visits: 7824 Borrowing from Dwain's sample data and Tally, here is my submission:`--== Dwain's setup ==--declare @t as table (ptr int identity, START time, ENDD time)insert into @t (START, ENDD)SELECT '09:30', '10:30' union allSELECT '10:00', '11:00' union allSELECT '10:30', '11:30' union allSELECT '09:30', '11:30' union allSELECT '11:00', '11:30';WITH Tally (n) AS( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM (VALUES (0),(0),(0),(0),(0),(0)) a(a) CROSS JOIN (VALUES (0),(0),(0),(0)) b(b))--== And MM's solution ==--select Tally.n as [Hour],SUM(calc.UpToENDD - calc.UpToSTART) as [TotalMinutes]from Tallyleft outer join @t as sourceon Tally.n between datepart(hour,convert(datetime,source.START)) and datepart(hour,convert(datetime,source.ENDD))outer apply ( select case when source.START is null then 0 when dateadd(hour,Tally.n,0)