Checking to see if a time is between two times

Question

Post reply

Checking to see if a time is between two times

greg.bull

SSCrazy

Points: 2093
More actions
February 22, 2012 at 6:05 am

#256207

Hi all,
I'm trying to break down events by hour of day as 00-23.
So if an event has a duration of 5 hours, from 22-03 then (this is the question) then I want to count the hours/events as
Hour EventCount
21 0
22 1
23 1
00 1
01 1
02 1
03 0
I created 24 case statements to check if an event was active - if the case hour was between the event start and end times. But, of course this fails utterly as the day wraps around its 24 hour clock.
As an example, checking at 02 hours, the event started at 22 and ended at 03, so I'd like to count this as a 1. But, 02 is not between 22 and 03.
Err, any ideas on how to do this chaps ?
Regards, Greg

Viewing 13 posts - 1 through 12 (of 12 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

DECLARE @start INT = 22, @duration INT = 5;

WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),

t2(N) AS (SELECT 1 FROM t1 x, t1 y),

t3(N) AS (SELECT 1 FROM t2 x, t2 y),

Tally(N) AS (SELECT TOP 48 N

FROM (SELECT 0 UNION ALL

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM t3 x, t3 y)a(N)),

eventCheck AS (SELECT RIGHT('0'+CAST(DATEPART(HOUR,tempDate) AS VARCHAR(2)),2) AS [hour], N

FROM Tally

CROSS APPLY (SELECT DATEADD(HOUR,N,'1901-01-01') AS tempDate) b)

SELECT [hour], CASE WHEN N > (@start-1) AND N < (@start+@duration) THEN 1 ELSE 0 END AS EventCount

FROM eventCheck

WHERE N >= (@start-1) AND N <= (@start+@duration)

Returns

hour EventCount

---- -----------

21 0

22 1

23 1

00 1

01 1

02 1

03 0

Provide sample data and DDL scripts in a readily consumable format if that isn't what you're after.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

greg.bull SSCrazy Points: 2093 More actions · Answer 2

Thanks, must beetle off and decipher this !

Regards, Greg

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 3

Having some sample data would help us write the query.

Normally, I would use DATEDIFF to determine the difference between two DATETIME values. I think the date portion is important. Suppose you have an event that runs 27 hours, from 10 (on the first) to 13 (on the second). You need the dates to be able to determine that it was 27 hours, not 3 hours.

greg.bull SSCrazy Points: 2093 More actions · Answer 4

Yep, I can do that. I'm sure it's part of the solution. It's the next part that baffles.

How do you use the known quantities of event start time, duration and endtime to then map to each of 24 hour pigeonholes to see if, for each hour, the event is active.

Regards,

Greg.

PS I must have another look at the last submission - although I think the SQL is too complex for me to get to grips with. A simpler - albeit with mode code lines - example would be appreciated.

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 5

greg.bull (2/22/2012)
How do you use the known quantities of event start time, duration and endtime to then map to each of 24 hour pigeonholes to see if, for each hour, the event is active.

Question: In your results, do you have one "pigeonhole" for Hour 01 on Feb 1st and one "pigeonhole" for Hour 01 on Feb 2nd, or do you have one "pigeonhole" for Hour 01 that covers every date?

R.P.Rozema SSChampion Points: 12307 More actions · Answer 6

Again: post some ddl with test data plus the expected output and we'll all be happy to help you. Or in other words: write a little script with a "create table" statement in it, plus some "insert" statements filling that table with test data and post this script. Then write down what the results should be given your set of test data and post that too. Hints and explanations on ho how to do this are a.o. to be found via the 'posting ettiquette'-link in my footer text. Without this information we can only keep on guessing as to what you need...

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

George H. SSCommitted Points: 1522 More actions · Answer 7

I'm not sure if this is the "correct" way to attack this problem...but one way could be to use a Dates table that holds a record for each day and an Hours table that holds 24 records (one for each hour of the day). Join them together to get a record for each day/hour that you need (based on your specific date range).

Then join the day/hour table to the Tasks table with a condition like:

day/hour table.day/hour BETWEEN Task.duration_start_time and Task.duration_end_time

This will grab each day/hour record that falls within the duration of the task...so the records you end up with could be given a value of "1" and any missing records could be given a value of "0" in the date/hour table.

You'll have to take minutes into account...but the idea is similar.

Does this make sense?

George

roncafferky SSC Journeyman Points: 75 More actions · Answer 8

I had this same challenge.. I report off clinical data, and needed to see how many hours a patient was in the Emergency Department. I was able to do it with just simple case statements..

You will need to do this case statement for every hour though..

But, in a nutshell, this is what I did:

,CASE WHEN CAST(Start_Event AS DATE) + CAST('00:00' AS DATETIME) BETWEEN Start_Event AND End_Event

THEN 1

WHEN CAST(End_Event AS DATE) + CAST('00:00' AS DATETIME) BETWEEN Start_Event AND End_Event

THEN 1

WHEN DATEPART(HH, Start_Event) = 0

Then 1

WHEN DATEPART(HH, End_Event) = 0

Then 1

ELSE 0

END AS [12AM]

Then, for 1AM, you would just change CAST('00:00' AS DATETIME) to CAST('01:00' AS DATETIME).. And then repeat for the remaining hours..

Let me know if this helps.. Thanks!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Have to agree, need DDL, sample data, and expected results.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 10

greg.bull (2/22/2012)
Hi all,
I'm trying to break down events by hour of day as 00-23.
So if an event has a duration of 5 hours, from 22-03 then (this is the question) then I want to count the hours/events as
Hour EventCount
21 0
22 1
23 1
00 1
01 1
02 1
03 0
I created 24 case statements to check if an event was active - if the case hour was between the event start and end times. But, of course this fails utterly as the day wraps around its 24 hour clock.
As an example, checking at 02 hours, the event started at 22 and ended at 03, so I'd like to count this as a 1. But, 02 is not between 22 and 03.
Err, any ideas on how to do this chaps ?
Regards, Greg

What do you want to do if an event is an hour and 30 minutes and starts at a quarter-to an hour?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

greg.bull SSCrazy Points: 2093 More actions · Answer 11

Dear All,

Thanks for the posts - this is an old one that I solved a while back (just using case statements as I recall). Didn't expect any further postings - but thanks anyway. So, you are free to align your expertise with other rookie problems !

Regards, Greg.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

greg.bull (4/9/2013)
Dear All,
Thanks for the posts - this is an old one that I solved a while back (just using case statements as I recall). Didn't expect any further postings - but thanks anyway. So, you are free to align your expertise with other rookie problems !
Regards, Greg.

Greg, thanks for coming back to this one. Please can you post the solution you chose?

- it may well be of use to others (one or two folks chimed in, remember)

- it's feedback for folks who attempted to help

- it's forum etiquette

Cheers

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden