SQL Join tables on Time Between Start and End
I have two tables:
Events (ID, Name, Time, Recorder) Video (ID, StartTime, EndTime, Recorder, Filename)
I wish to join the Event data to the video data, so that for every event I get the video filename. The recorder field is used to specify which recorder was operable at the event's time, and assists with multiple recorders recording video at the same time.
If i wasn't concerned about the events which have no video then this is fine (I can get the SQL), however in my case i wish to show the closest video filename and the seconds difference.
1, EV1, 2012-01-01 12:00, A 2, EV2, 2012-01-01 13:00, B 3, EV3, 2012-01-01 12:15, B 4, EV4, 2012-01-01 11:45, A
1, 2012-01-01 12:00, 2012-01-01 12:30, A, 1.mpg 2, 2012-01-01 13:00, 2012-01-01 13:30, A, 2.mpg 3, 2012-01-01 12:00, 2012-01-01 12:30, B, 3.mpg
Result (EventID, VideoID, Filename, IsBetween, SecondsDifference)
1, 1, 1.mpg, TRUE, 0 2, 3, 3.mpg, FALSE, 1800 //1800 seconds from the end of video 3 3, 3, 3.mpg, TRUE, 900 4, 1, 1.mpg, FALSE, 900 //900 seconds from the start of video 1
I would be even nicer if the closest video did not take the recorder into account (but the first bounds (Start and End) check to take it into account) If this is too difficult then thats fine.
It's a little clunky, but here's what I came up with:
SELECT * FROM ( SELECT a.ID AS EventID, b.ID AS VideoID, b.Filename, ( CASE WHEN a.Time < b.StartTime THEN UNIX_TIMESTAMP(b.StartTime) - UNIX_TIMESTAMP(a.Time) WHEN a.Time > b.EndTime THEN UNIX_TIMESTAMP(a.Time) - UNIX_TIMESTAMP(b.EndTime) END ) AS distance_factor FROM `Events` a CROSS JOIN video b WHERE NOT EXISTS ( SELECT NULL FROM Video WHERE a.Time BETWEEN StartTime AND EndTime ) ) c WHERE c.distance_factor = ( SELECT MIN(CASE WHEN d.Time < e.StartTime THEN UNIX_TIMESTAMP(e.StartTime) - UNIX_TIMESTAMP(d.Time) WHEN d.Time > e.EndTime THEN UNIX_TIMESTAMP(d.Time) - UNIX_TIMESTAMP(e.EndTime) END) FROM `Events` d CROSS JOIN video e WHERE d.ID = c.EventID ) GROUP BY c.EventID
This returns events whose dates don't fall between any of the time ranges of any video, but then returns the video that falls the closest to that event date.
The only thing right now is that there are some videos where the seconds difference is exactly the same. I don't know if you want it to return 2 rows, but for now, I put in the GROUP BY to just select one.
Let me know how that works.
My final result was:
SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT *, (CASE WHEN Time < StartTime THEN UNIX_TIMESTAMP(StartTime) - UNIX_TIMESTAMP(Time) WHEN Time > EndTime THEN UNIX_TIMESTAMP(Time) - UNIX_TIMESTAMP(EndTime) END ) AS SecondsDifference FROM ( SELECT * FROM Events E LEFT JOIN Video V ON (E.Time >= V.StartTime AND E.Time <= V.EndTime) WHERE DVID IS NULL GROUP BY E.EventID ) A ORDER BY A.EventID, A.SecondsDifference ) B GROUP BY EventID ) C WHERE C.SecondsDifference IS NOT NULL
Essentially this first gets all events without any video, then joins this result on the entire video list, orders it by the EventID and ClosestSeconds, and then Groups the result by the EventID to remove the duplicates. Finally, I needed to remove any Events where the SecondsDifference was null.
It produces the same result as Zane's answer.
Thanks a lot Zane.