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.

CIC AdminAsked:
Who is Participating?
zephyr_hex (Megan)DeveloperCommented:
ah, yes, my original query will not account for the 15 min.

you can nest the functions:

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

zephyr_hex (Megan)DeveloperCommented:
use dateadd in your query.

for example:

select * from mytable
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.
zephyr_hex (Megan)DeveloperCommented:
i meant to include a link to the dateadd syntax:
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

FROM YourTable AS T
INNER JOIN      (      SELECT       StartDate      = DATEADD(HH ,-6 ,DatePivot)
                                    ,EndDate      = DatePivot
                        FROM      (      SELECT DATEADD(       HH
                                                                  ,(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
zephyr_hex (Megan)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?
I only used the subquery to avoid the declaration of variables and for testing each one separatly and view the filter criteria
CIC AdminAuthor 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.

check my solution kbliven, the query calculates the begin and end of the past frame period
CIC AdminAuthor 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

DATEADD(HH, DATEDIFF(HH, 0, dateadd(hh,-6,getdate())), 0)


DATEADD(HH, DATEDIFF(HH, 0, getdate()), 0)

in my where clause to calculate the time frame.

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.

All Courses

From novice to tech pro — start learning today.