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.