SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Determine time elapased by hour


Determine time elapased by hour

Author
Message
m.dunster
m.dunster
SSC Eights!
SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)

Group: General Forum Members
Points: 874 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 all
SELECT '10:00', '11:00'
union all
SELECT '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.5
10 - 2
11 - 0.5

I 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
Sean Lange
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148491 Visits: 18575
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 all
SELECT '10:00', '11:00'
union all
SELECT '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.5
10 - 2
11 - 0.5

I 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



Thank 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 Modens 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
m.dunster
SSC Eights!
SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)

Group: General Forum Members
Points: 874 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
Mark Cowne
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14734 Visits: 26572
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 h
INNER JOIN @t t ON t.ENDD > h.hrStart AND t.START < h.hrEnd
GROUP BY h.hrStart
ORDER BY h.hrStart;



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




m.dunster
m.dunster
SSC Eights!
SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)

Group: General Forum Members
Points: 874 Visits: 266
Thanks Mark. I'll give it a go. The midnight issue shouldn't be a problem.
dwain.c
dwain.c
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44173 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 all
SELECT '10:00', '11:00' union all
SELECT '10:30', '11:30' union all
SELECT '09:30', '11:30' union all
SELECT '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 a
CROSS APPLY
(
SELECT StartHr=DATEPART(hour, START), EndHr=DATEPART(hour, ENDD)
) b
CROSS 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.n
GROUP 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
m.dunster
SSC Eights!
SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)

Group: General Forum Members
Points: 874 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
brad.mason5
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2216 Visits: 2160
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 all
SELECT '10:00', '11:00'
union all
SELECT '10:30', '11:30'
union all
SELECT '13:30', '15:30'
union all
SELECT '18:30', '19:00'
union all
SELECT '10:00', '10:30'
union all
SELECT '03:00', '08:30'
union all
SELECT '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 than
1 hour this will generate a new record for this.

*******************************************/
IF OBJECT_ID('tempdb..#LightHour','u') IS NOT NULL
DROP TABLE #LightHour

;WITH ExpandHours
AS
(
SELECT t.ptr
,e.RowNum
,StartTime = CAST(e.StartTime AS TIME)
,EndTime = CAST(e.EndTime AS TIME)
,OriginalStartTime = t.START
,OriginalEndTime = t.ENDD
FROM @t t
CROSS 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
),
Final
AS
(
SELECT ptr
,StartTime = CASE WHEN StartTime < OriginalStartTime
THEN OriginalStartTime
ELSE StartTime
END
,EndTime = CASE WHEN OriginalEndTime < EndTime
THEN OriginalEndTime
ELSE EndTime
END
FROM ExpandHours
UNION
SELECT ptr
,StartTime = (SELECT MAX(c.EndTime) FROM ExpandHours c WHERE i.ptr = c.ptr)
,EndTime = OriginalEndTime
FROM ExpandHours i
)
SELECT ptr
,Start = StartTime
,Endd = EndTime
INTO #LightHour
FROM Final
WHERE StartTime <> EndTime
UNION
--if under 1 hour then include here
SELECT *
FROM @t
WHERE DATEDIFF(mi, START, ENDD) < 60


IF OBJECT_ID('tempdb..#Final','u') IS NOT NULL
DROP TABLE #Final


SELECT LighHour =hrStart
,TotalTimeOn_MI = SUM(CASE WHEN t.Start >= hrStart AND t.Start < hrEnd
THEN DATEDIFF(mi, t.Start, t.Endd)
ELSE 0
END)
--,ptr
INTO #Final
FROM #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)
) h
GROUP BY h.hrStart
-- ,ptr

SELECT *
FROM #Final
WHERE TotalTimeOn_MI > 0





mister.magoo
mister.magoo
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26472 Visits: 7939
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 all
SELECT '10:00', '11:00' union all
SELECT '10:30', '11:30' union all
SELECT '09:30', '11:30' union all
SELECT '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 Tally
left outer join @t as source
on 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)<convert(datetime,source.START) then datediff(minute,dateadd(hour,Tally.n,0),convert(datetime,source.START))
else 0
end as UpToSTART,
case when source.ENDD is null then 0
when Tally.n=datepart(hour,convert(datetime,source.ENDD)) then datediff(minute,dateadd(hour,Tally.n,0),convert(datetime,source.ENDD))
else 60
end as UpToENDD
) calc
group by Tally.n
order by Tally.n;




MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • brad.mason5
    brad.mason5
    SSCrazy
    SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

    Group: General Forum Members
    Points: 2216 Visits: 2160
    Monster Maghoul thank you for sharing! This is awesome now I just need to understand what is going on here. This guy will perform well under load too!
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum








































































































































































    SQLServerCentral


    Search