Calculating difference between two times with a twist (between 9am and 5pm)

Question

Post reply

Calculating difference between two times with a twist (between 9am and 5pm)

RedBirdOBX

SSCommitted

Points: 1836
More actions
March 25, 2014 at 11:44 am

#305810

I am seeking help on this one. I've tinkered and experimented and can't seem to connect the dots here.
I have Two Time fields in a table. Time(0). An "opening time" and a "closing time". They can hold any legit time.
I want to calculate in a SELECT Statement how many minutes within this range are within 9am to 5pm (which I'll convert to hours).
For example, here's an easy example:
OPEN: 9:00:00
CLOSE: 17:00:00
8 Hours/480 minutes
I could get this easy enough with a DATEDIFF function.
But what about:
OPEN: 08:00:00
CLOSE: 18:00:00
10 Hours total but only 8 of those 10 are within 9am-5pm.
Or what about:
OPEN: 10:00:00
CLOSE: 20:00:00
10 Hours total but only 7 are within 9am-5pm range.
I can calculate the total hours/minutes between the two times but not within that special range. I'm pretty sure I should use DATEDIFF but just can't seem to connect the dots in head.
Any suggestions??

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Maybe some brute force can help you.

CREATE TABLE #Test(

OpenTimetime,

CloseTimetime)

INSERT #Test

SELECT '09:00:00', '17:00:00' UNION ALL

SELECT '08:00:00', '18:00:00' UNION ALL

SELECT '10:00:00', '20:00:00'

SELECT *,

DATEDIFF(MI, CASE WHEN OpenTime < '09:00:00' THEN '09:00:00' ELSE OpenTime END

,CASE WHEN CloseTime > '17:00:00' THEN '17:00:00' ELSE CloseTime END) AS [Minutes],

DATEDIFF(HH, CASE WHEN OpenTime < '09:00:00' THEN '09:00:00' ELSE OpenTime END

,CASE WHEN CloseTime > '17:00:00' THEN '17:00:00' ELSE CloseTime END) AS [Hours]

FROM #Test

GO

DROP TABLE #Test

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

RedBirdOBX SSCommitted Points: 1836 More actions · Answer 2

I was actually leaning towards a temp table....I will give it a try.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

RedBirdOBX (3/25/2014)
I was actually leaning towards a temp table....I will give it a try.

Note that the temp table I used is to have the sample data. You don't need to create it as part of your solution. It would be nice if you provide your sample data in this format so we don't waste time on creating it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

RedBirdOBX SSCommitted Points: 1836 More actions · Answer 4

I'll post back what I came up with and welcome feedback. Hopefully within the hour.....