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
CIC AdminAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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
zephyr_hex (Megan)DeveloperCommented:
i meant to include a link to the dateadd syntax:
http://msdn.microsoft.com/en-us/library/ms186819.aspx
0
penyCuicasCommented:
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
                        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
            )
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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?
0
penyCuicasCommented:
I only used the subquery to avoid the declaration of variables and for testing each one separatly and view the filter criteria
0
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.

Thanks!
0
penyCuicasCommented:
check my solution kbliven, the query calculates the begin and end of the past frame period
0
zephyr_hex (Megan)DeveloperCommented:
ah, yes, my original query will not account for the 15 min.

you can nest the functions:

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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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)

and

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

in my where clause to calculate the time frame.

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.