RoyalTS RoyalTS - 6 months ago 11
SQL Question

Range join without duplicates

I have a table that contains only datetimes, like so:

datetime
2016-05-16 10:00:00
2016-05-16 10:30:00
2016-05-16 11:00:00
2016-05-16 11:30:00
2016-05-16 12:00:00
2016-05-16 12:30:00
2016-05-16 13:00:00
2016-05-16 13:30:00
2016-05-16 14:00:00
2016-05-16 14:30:00
2016-05-16 15:00:00
2016-05-16 15:30:00
2016-05-16 16:00:00
2016-05-16 16:30:00
2016-05-16 17:00:00
2016-05-16 17:30:00
2016-05-16 18:00:00
2016-05-16 18:30:00
2016-05-16 19:00:00
2016-05-16 19:30:00


I want to range join this table against a table that tags certain time intervals as
closed
:

start stop closed
2016-05-04 16:56:07 2016-05-04 17:26:20 TRUE
2016-05-13 09:29:23 2016-05-14 21:27:17 TRUE
2016-05-14 21:00:39 2016-05-15 20:48:37 TRUE
2016-05-16 10:26:56 2016-05-16 11:48:24 TRUE
2016-05-16 11:33:42 2016-05-16 12:24:31 TRUE
2016-05-16 13:08:15 2016-05-16 14:27:42 TRUE
2016-05-16 15:46:07 2016-05-16 16:19:38 TRUE
2016-05-16 16:33:49 2016-05-16 17:52:31 TRUE
2016-05-16 16:34:12 2016-05-16 17:52:50 TRUE
2016-05-16 16:35:00 2016-05-16 17:53:33 TRUE


As you can see, some of these intervals overlap and so when I range join the two tables with

SELECT d.*, c.closed
FROM dates d
LEFT JOIN closures c
ON c.start <= d.datetime AND d.datetime <= c.stop


I get duplicate rows in the resulting table. Is there any way of tagging the datetimes in the left table as closed if at least one interval straddles it without duplicating any rows?

Answer

You can use EXISTS() that does exactly what you asked for :

SELECT d.*,
       CASE WHEN EXISTS(SELECT 1 FROM closures c
                        WHERE c.start <= d.datetime AND d.datetime <= c.stop)
            THEN 'TRUE'
            ELSE 'FALSE'
       END as Your_Ind
FROM dates d

Or just use DISTINCT :

SELECT DISTINCT d.*, c.closed 
FROM dates d 
LEFT JOIN closures c
ON c.start <= d.datetime AND d.datetime <= c.stop

If you are intrested only in the records that are closed, then use EXISTS() on the WHERE clause.

 SELECT d.*,'TRUE'
FROM dates d
WHERE EXISTS(SELECT 1 FROM closures c
             WHERE c.start <= d.datetime AND d.datetime <= c.stop)
Comments