# Query to Calculate 6-hour Intervals

I have a table that is continuously capturing incidents.  The incident can take up to 10 or 15 minutes to close out.  What i need is to report 4 times per day exceptions to certain incidents during the previous 6-hour interval.

The time frames will be :

Midnight - 6AM
6AM - Noon
Noon - 6PM
6PM - Midnight

I have the query running fine except for the time frame issue.  I plan on creating a job that runs at 15 past the hour 4 times per day. (15 past the hour to give the incident time to close)

What is the easiest/simplest way to create a query to select on that time frame?

For the sake of simplicity, consider the table as something like :

Inci. #      Start_Dttm                           Value
11-4545   2011-12-14 02:11:59.000   E01
11-4547   2011-12-14 09:10:34.000   E03
11-4549   2011-12-14 14:28:14.000   E02
11-4557   2011-12-14 23:14:59.000   E03

Running the task at 6:15PM (18:15:00) on 2011-12-14 would query between 2011-12-14 12:00:00 and 2011-12-14 17:59:59 and produce record 11-4549

I also need to make sure the previous date is taken into consideration, since running it at 00:15:00 on 12-15 needs to query against 18:00:00 and 23:59:59 on 12-14.

Thanks!
LVL 5
###### Who is Participating?

DeveloperCommented:
ah, yes, my original query will not account for the 15 min.

you can nest the functions:

select * from mytable
where
and Start_Dttm <= getdate()

0

DeveloperCommented:

for example:

select * from mytable
where
Start_Dttm >= dateadd(hh,-6,getdate()) and Start_Dttm <= getdate()

This will include records where the Start_Dttm value is 6 hours earlier to the current time.

and dateadd will handle the transition between days.  it knows that 6 hours back from 00:15 is yesterday.
0

DeveloperCommented:
http://msdn.microsoft.com/en-us/library/ms186819.aspx
0

Commented:
The subquery in the second table calculates the frame time that you need

SELECT T.*
FROM YourTable AS T
INNER JOIN      (      SELECT       StartDate      = DATEADD(HH ,-6 ,DatePivot)
,EndDate      = DatePivot
,(DATEPART(HH ,GETDATE()) / 6) * 6
, CONVERT(DATETIME ,CONVERT(VARCHAR(8) ,GETDATE() ,112))
) AS DatePivot
) AS T
) AS Period
ON      (            T.Start_Dttm >= Period.StartDate
AND T.Start_Dttm <      Period.EndDate
)
0

DeveloperCommented:
i'm not sure why the query needs a subquery ?  why not just a straighforward use of dateadd in the where criteria like my example?
0

Commented:
I only used the subquery to avoid the declaration of variables and for testing each one separatly and view the filter criteria
0

Author Commented:
Thanks Zephyr!  I think that gets it pretty close.  I totally forgot about using negative numbers with dateadd, so thanks for that too.

One thing, though, since it is run at 15 after the hour, won't your query return results from, say, Noon to 6:15pm instead of Noon to 6pm?  I need to run it a few minutes after the top of the hour to make sure all the incidents have closed out completely.

I think this is also returning the previous 6 hours, not the last full 6-hour block.  If you fixed the minor issue above then I would assume it would have to be run sometime in the following hour.  I don't think this should be a problem, just want to make sure i understand it.

Thanks!
0

Commented:
check my solution kbliven, the query calculates the begin and end of the past frame period
0

Author Commented:
Thanks zephyr_hex.  I ended up using most of your code.  Found a little additional snippit to just truncate the hours instead of subtracting 15, but the -6 is what got me through so thanks for that.

For future reference, i used

and

in my where clause to calculate the time frame.

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.