CODEWITHSUNDEEP
sqlsql-servert-sqldate
BenLevi
BenLevi

Reputation: 7

SQL finding overlapping dates given start and end date

Given a data set in MS SQL Server 2012 where travelers take trips (with trip_ID as UID) and where each trip has start_date and an end_date, I'm looking to find the trip_ID's for each traveler where trip's overlap and the range of that overlap. So if the initial table looks like this: 

| trip_ID | traveler | start_date | end_date   | trip_length |
|---------|----------|------------|------------|-------------|
|    AB24 |    Alpha | 2017-01-29 | 2017-01-31 |            2|
|    BA02 |    Alpha | 2017-01-31 | 2017-02-10 |           10| 
|    CB82 |  Charlie | 2017-02-20 | 2017-02-23 |            3|
|    CA29 |    Bravo | 2017-02-26 | 2017-02-28 |            2|
|    AB14 |  Charlie | 2017-03-06 | 2017-03-08 |            2|
|    DA45 |    Bravo | 2017-03-26 | 2017-03-29 |            3|
|    BA22 |    Bravo | 2017-03-29 | 2017-04-03 |            5|

I'm looking for a query that will append three columns to the original table: overlap_id, overlap_start, overlap_end. The idea is that each row will have a value (or NULL) for an overlapping trip along with the start and end dates for overlap itself. Like this:

| trip_ID | traveler | start_date | end_date   |trip_length|overlap_id  |overlap_start| overlap_end| 
|---------|----------|------------|------------|-----------|------------|-------------|------------|
|    AB24 |    Alpha | 2017-01-29 | 2017-01-31 |          2|BA02--------|2017-01-31---|2017-01-31--|
|    BA02 |    Alpha | 2017-01-31 | 2017-02-10 |         10|AB24--------|2017-01-31---|2017-01-31--|
|    CB82 |  Charlie | 2017-02-20 | 2017-02-23 |          3|NULL--------|NULL---------|NULL--------|
|    CA29 |    Bravo | 2017-02-26 | 2017-02-28 |          2|NULL--------|NULL---------|NULL--------|
|    AB14 |  Charlie | 2017-03-06 | 2017-03-08 |          2|NULL--------|NULL---------|NULL--------|
|    DA45 |    Bravo | 2017-03-26 | 2017-03-29 |          3|BA22--------|2017-03-28---|2017-03-29--|
|    BA22 |    Bravo | 2017-03-28 | 2017-04-03 |          5|DA45--------|2017-03-28---|2017-03-29--|

I've tried variations of Overlapping Dates in SQL to inform my approach but it's not returning the right answers. I'm only looking for overlaps for the same traveler (i.e., within Alpha or Bravo, not between Alpha and Bravo).

For the overlap_id column, I think the code would have to test if a trip's start_date plus range(0, trip_length) returns a value within the range of dates between start_date and end_date for any other trip where the traveler is the same, then the trip_id is updated to equal the id of the matching trips. If this is the right concept, I'm not sure how to make a variable out of trip_length so I test a range of values for it, i.e., run this for all values of trip_length - x until trip_length - x = 0. 

--This might be the bare bones of an answer
update table
set overlap_id = CASE 
WHEN ( DATEADD(day, trip_length, start_date) = SELECT (DATEADD(day, trip_length, start_date) from table where traveler = traveler)

Upvotes: 0

Views: 1270

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272406

You can join the table with itself (the join condition is described here):

SELECT t.*, o.trip_ID, o.start_date, o.end_date
FROM t
LEFT JOIN t AS o ON t.trip_ID <> o.trip_ID      -- trip always overlaps itself so exclude it
                 AND o.traveler = t.traveler    -- same traveller
                 AND t.start_date <= o.end_date -- overlap test
                 AND t.end_date >= o.start_date

Upvotes: 1

Related Questions