CODEWITHSUNDEEP
sqlsql-server-2012
sky_limit
sky_limit

Reputation: 133

Datediff in WHERE clause not working

I have the following script. I need to get all the visits where customers came back within 30 days. Everything works fine in the script but the datediff in the where clause. Could someone please help me fix the logic?

select OverallNumber,VisitID,coalesce(CheckInDateTime,ServiceDateTime) as ArrivalDateTime, DepartDateTime
from Visits 
where OverallNumber='100'
and DepartDateTime is not null
and coalesce(CheckInDateTime,ServiceDateTime) is not null
--and DATEDIFF(day,MIN(coalesce(CheckInDateTime,ServiceDateTime)), Max(DepartDateTime))<30
order by ArrivalDateTime;

Current data:

OverallNumber   VisitID ArrivalDateTime DepartDateTime
100              1      6/13/2015       6/15/2015
100              2      11/5/2015       11/7/2015
100              3      11/20/2015      11/25/2015

Desired:
OverallNumber   VisitID ArrivalDateTime DepartDateTime
    100              2      11/5/2015       11/7/2015
    100              3      11/20/2015      11/25/2015

In this example customer came back twice from 11/5/2015 to 11/25/2015. Please help. Thanks.

Upvotes: 0

Views: 835

Answers (1)

JamieD77
JamieD77

Reputation: 13949

SELECT OverallNumber,
        VisitID,
        ArrivalDateTime,
        DepartDateTime
FROM (
    SELECT
        OverallNumber,
        VisitID,
        ArrivalDateTime,
        DepartDateTime,
        LAG(DepartDateTime, 1, NULL) OVER (PARTITION BY OverallNumber ORDER BY ArrivalDateTime) PreviousDateDiff,
        LEAD(ArrivalDateTime, 1, NULL) OVER (PARTITION BY OverallNumber ORDER BY ArrivalDateTime) NextDateDiff
    FROM (
        SELECT  
            *, 
            COALESCE(CheckInDateTime,ServiceDateTime) ArrivalDateTime 
        FROM    
            Visits
        WHERE
            OverallNumber = '100'
            AND DepartDateTime IS NOT NULL) t         
    ) v
WHERE 
    ArrivalDateTime < DATEADD(day, 30, PreviousDateDiff)
    OR DATEADD(day, -30, NextDateDiff) < DepartDateTime
ORDER BY
    ArrivalDateTime;

The Lag will give you the previous DepartDateTime and the Lead function will give you the next ArrivalDateTime

Upvotes: 1

Related Questions