Trying to figure out a query that could work for multiple situations. In a nutshell the data can be in one of twosituations. Lets say I'm looking for the record_id for events happened during a given time frame: 6/26/2012 10:00AM and 6/27/2012 11:00AM The records can look like this in the database:
Record Event Time
1 Start 6/26/2012 10:05AM
1 End 6/26/2012 10:45AM
2 Start 6/26/2012 09:55AM
2 End 6/26/2012 11:05AM
Getting record 1 is easy, just using the between function, but I'm stumbling trying to figure out a query to return both records 1 and 2.
Suggestions?
You can simply widen the range, but this may bring back different records than you intend.
SELECT RECORD, EVENT, TIME
FROM SO_RecordEvent AS R
WHERE TIme BETWEEN '6/26/2012 9:55AM' AND '6/26/2012 11:06AM'
This will return all records for anything that has either a start or end time within the range, including the associated records falling outside of the range (In other words records that only have one time in the range - but began before or ended after - it will still show the start or end time outside of it) and allow you you shorten your range.
;WITH X AS
(
SELECT RECORD,EVENT,TIME FROM SO_RecordEvent
)
SELECT R.RECORD,R.EVENT,R.TIME
FROM SO_RecordEvent AS R
INNER JOIN X ON R.Record = X.Record
WHERE X.TIme BETWEEN '6/26/2012 10:05AM' AND '6/26/2012 11:05AM'
GROUP BY R.RECORD,R.EVENT,R.TIME
But I think you may really want something like this, which truly gives you everything that was started during that time, even if it started AND ended outside of the range, as is your record 2 example.
EDIT
Changed logic - instead of addressing situations decided to think about it this way - anything that started within start and end, anything that ended within start and end, and anything that started before and ended after. I think this covers anything that runs during this time (starts before ends within, starts and ends within, starts within and ends after, and starts before and ends after)
SELECT X.RECORD,X.TIME AS STARTTIME,Y.TIME AS ENDTIME
FROM SO_RecordEvent AS X
INNER JOIN SO_RecordEvent Y ON Y.Record = X.Record AND Y.EVENT = 'END'
WHERE X.EVENT = 'START'
AND
((X.TIME >= '6/26/2012 10:00AM' AND X.TIME <= '6/26/2012 11:00AM')
OR (Y.TIME >= '6/26/2012 10:00AM' AND Y.TIME <= '6/26/2012 11:00AM')
OR (X.TIME <= '6/26/2012 10:00AM' AND Y.TIME >= '6/26/2012 11:00AM'))
Variables to play with:
DECLARE @START datetime, @END datetime
SET @START = '6/26/2012 10:00AM'
SET @END = '6/26/2012 11:00AM'
SELECT X.RECORD,X.TIME AS STARTTIME,Y.TIME AS ENDTIME
FROM SO_RecordEvent AS X
INNER JOIN SO_RecordEvent Y ON Y.Record = X.Record AND Y.EVENT = 'END'
WHERE X.EVENT = 'START'
AND
((X.TIME >= @START AND X.TIME <= @END)
OR (Y.TIME >= @START AND Y.TIME <= @END)
OR (X.TIME <= @START AND Y.TIME >= @END))
I'd probably join the table to itself - something like this:
SELECT *
FROM
Table leftside
JOIN Table rightside
ON leftside.Record = rightside.Record
WHERE
leftside.Event = 'Start'
and rightside.Event = 'End'
and [whatever time you want] >= leftside.Time
and [whatever time you want] <= rightside.Time
EDIT: Example to demonstrate
create table thingy (
record int,
event varchar(10),
time datetime )
insert thingy (record,event,time) values (1,'Start','6/26/2012 10:05AM')
insert thingy (record,event,time) values (1,'End','6/26/2012 10:45AM ')
insert thingy (record,event,time) values (2,'Start','6/26/2012 09:55AM')
insert thingy (record,event,time) values (2,'End','6/26/2012 11:05AM')
DECLARE @mytime datetime
SET @mytime = '6/26/2012 9:58AM'
SELECT *
FROM
thingy leftside
JOIN thingy rightside
ON leftside.Record = rightside.Record
WHERE
leftside.Event = 'Start'
and rightside.Event = 'End'
and @mytime >= leftside.Time
and @mytime <= rightside.Time
SET @mytime = '6/26/2012 10:10AM'
SELECT *
FROM
thingy leftside
JOIN thingy rightside
ON leftside.Record = rightside.Record
WHERE
leftside.Event = 'Start'
and rightside.Event = 'End'
and @mytime >= leftside.Time
and @mytime <= rightside.Time
Seems to give what I would expect - 1 row for the first query, 2 for the second.
Get record #s of all events that fall within the given range:
SELECT Record
FROM atable
WHERE Time BETWEEN @StartTime AND @EndTime
Now just get all rows whose record #s match those in the above query's result set, i.e. like this:
SELECT *
FROM atable
WHERE Record IN (
SELECT Record
FROM atable
WHERE Time BETWEEN @StartTime AND @EndTime
)
The simplest way to do this would be using the following:
WITH recs AS (
SELECT Record, MIN(tme) AS startTime, MAX(tme) AS endTime
FROM records
GROUP BY Record
)
SELECT * FROM
records
WHERE startTime >= @eventsAfter
AND endTime <= @eventsBefore
(this is how I interpreted your question, at least)
