Traveller travelled based on data range

I want to return all the travellers who are currently travelling when I supply a From and To date. Meaning if I travel from 1 Jan 2022 until 10 Jan 2022 and I supply a From date as 5 Jan 2022 and a To date 15 Jan 2022 I must be retuned as I was travelling during that time period. So in my code below Mike must be returned.

DECLARE @DateFrom      DATE         = '2022-01-05',
        @DateTo        DATE         = '2022-01-15'

DROP TABLE IF EXISTS #Dates

CREATE TABLE #Dates
(
    DepartureDate  Date       NULL,
    ReturnDate     Date       NULL,
    Name           VARCHAR(8) NULL
)

INSERT INTO #Dates (DepartureDate, ReturnDate, Name)
VALUES ('2022-01-01', '2022-01-10', 'Mike' )

SELECT * 
FROM   #Dates
WHERE  DepartureDate >= @DateFrom
AND    ReturnDate    <= @DateTo

If you select date range between '2021-12-01' and '2021-12-05' then Mike did not travel so should not be returned. But if you select date range between '2022-01-05' and '2022-01-15' then Mike should be returned as Mike did travel in that date range even though he did not travel all the days.

CodePudding user response：

I guess your problem is your WHERE clause doesn't work for all cases to support your problem description.

This is one way you could write it to fix it:

SELECT * 
FROM #Dates
WHERE  
    (DepartureDate >= @DateFrom AND ReturnDate <= @DateTo) -- Traveled only between provided date range
    OR (DepartureDate < @DateFrom AND ReturnDate > @DateTo) -- Traveled the entire date range, possibly longer 
    OR (DepartureDate < @DateFrom AND ReturnDate <= @DateTo AND ReturnDate >= @DateFrom) -- Started traveling before date range but finished traveling within it 
    OR (DepartureDate >= @DateFrom AND DepartureDate <= @DateTo AND ReturnDate > @DateTo) -- Started traveling within the date range but finished traveling after

Please see the comments on each case.

Alternatively you can write this as 4 SELECT statements (one for each case) with a UNION ALL clause too. Sometimes the SQL Server Engine produces a more efficient query plan when writing queries this way as opposed to multiple OR predicates:

-- Traveled only between provided date range
SELECT * 
FROM #Dates
WHERE  
    DepartureDate >= @DateFrom 
    AND ReturnDate <= @DateTo

UNION ALL

-- Traveled the entire date range, possibly longer
SELECT * 
FROM #Dates
WHERE 
    DepartureDate < @DateFrom 
    AND ReturnDate > @DateTo 
    
UNION ALL

-- Started traveling before date range but finished traveling within it 
SELECT * 
FROM #Dates
WHERE
    DepartureDate < @DateFrom 
    AND ReturnDate <= @DateTo
    AND ReturnDate >= @DateFrom

UNION ALL

-- Started traveling within the date range but finished traveling after
SELECT * 
FROM #Dates
WHERE
    DepartureDate >= @DateFrom 
    AND DepartureDate <= @DateTo
    AND ReturnDate > @DateTo

You'd have to test both ways to see which one performs better for your particular case.

Side note, don't use SELECT * because it's an anti-pattern. Rather you should explicitly list out only the columns the query needs.

CodePudding user response：

The overlapping intervals problem! Check that each interval starts before the other interval ends:

SELECT * 
FROM   #Dates
WHERE  DepartureDate <= @DateTo
AND    ReturnDate    >= @DateFrom