SQL Join tables on Time Between Start and End

Question

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.

EDIT

Sample Data

Events

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

Video

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

BONUS

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.

Can you give sample data and expected result? Is startTime,EndDateTime and Time fields contain both date and time ? — Sudhakar B, Jun 14 '12 at 04:20
Should it return the one based on if it(the input time) is closer to the starttime or endtime? Also, is the `ID` in the video field referenced to the `ID` field in the `Events` table? — Zane Bien, Jun 14 '12 at 04:24
@Zane Bien - It should simply return the closest video file (from the start or end) and no, the ID's are not linked between the two tables — Simon, Jun 14 '12 at 04:50
Got it! I'll see what I can come up with. I love challenges like this. Oh, by the way, will there ever be an event which is directly in between a video date range? (i.e. `'2012-01-01 12:15'`) If so, should it return the difference between it and the start date if it's closer to the start date and vice versa for end date? — Zane Bien, Jun 14 '12 at 04:51
@Zane Bien, Yes there could be an event which is between a date range. In this instance, my only requirement is I need to differentiate between events that fall between video times and ones that are outside. There could maybe be a `IsBetween` field returned if required — Simon, Jun 14 '12 at 04:58
3rd record in the example result set: You have `TRUE` and a `SecondsDifference` of 900. So if the date falls within a date range, should it be the difference between the start or end date... or should it be the difference of whichever one is closer (start or end) ? — Zane Bien, Jun 14 '12 at 05:02
@Zane Bien, It doesnt matter if its between the range, whatever is easier. I'm actually concerned with any events that DON'T have any video. — Simon, Jun 14 '12 at 05:12
Okay, hmm... so you just want events in which their date falls outside of the ranges of all videos, and just return the closest one (whether it may be the closest to the start or end date if the event is before or after the video respectively), with the seconds difference. — Zane Bien, Jun 14 '12 at 05:17
@Zane Bien, Yes sorry for the confusion... I can currently get the events which have video. That'll make the query easier. — Simon, Jun 14 '12 at 05:27
Got it! By the way, it looks like the second row in your example result set is supposed to be `2, 2, 2.mpg, TRUE, 0` because 13:00 falls right on the start of video 2 — Zane Bien, Jun 14 '12 at 05:39
@Zane Bien, You are sort of correct - now that were only getting the events without video then yes, however if we are to list all the events with video I need to take the recorder into account (so the join is on the `recorder` field too!) — Simon, Jun 14 '12 at 05:43

score 2 · Accepted Answer · answered Jun 14 '12 at 06:11

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.

+1 Thanks - this does produce the correct output however you are right about clunky :). I managed to optimize it a bit and its about 10x faster — Simon, Jun 14 '12 at 07:22

score 2 · Answer 2 · answered Jun 14 '12 at 08:41

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.

SQL Join tables on Time Between Start and End

2 Answers2

Linked