Compare a date with other dates within a single column in snowflake

user_17096408 0
Viewed 271

I have a following table :

**Person Id LOCATION   VISIT_DATE**
12345     LOCATION1  27/01/2020
12345     LOCATION2  12/02/2018
12345     LOCATION2  09/08/2018
12345     LOCATION2  12/07/2018
12345     LOCATION2  31/01/2020
12345     LOCATION2  07/10/2019
12345     LOCATION2  30/04/2019
12345     LOCATION3  28/09/2018
12345     LOCATION3  18/02/2020

I want to compare the date for LOCATION1 to dates for other locations (here: LOCATION 2 and 3)

What i want to achieve from here is, i want to check if a particular person_id (here : 12345) has LOCATION 2 or LOCATION 3 visit date within 4 weeks of a visit date to LOCATION 1.

Here we can see the person has two dates for location 2 and 3 that meets the above mentioned criteria (i.e dates are within 4 weeks of a visit date to location 1), Such members should be flagged with 'Y', if they don't have location 2 and 3 dates within the 4 weeks of location 1 date then they should be flagged as 'N'.

I know DATEDIFF function with Week or DAYS will come into handy for this. I tried with different CASE statements but i am not able to achieve the result i want.

Please help !

Note that in my table for a particular PersonID there will be only one location : LOCATION 1 but can have multiple LOCATION 2 and 3

snowflake-cloud-data-platform sql
1 Answers

Using JOIN:

SELECT DISTINCT t1.*
FROM tab t1
JOIN tab t2
  ON t1.PersonId = t2.PersonId
 AND t2.Location = 'LOCATION2'
 AND ABS(DATEDIFF(day, t1.VISIIT_DATE, t2.VISIT_DATE)) <= 28
JOIN tab t3 
  ON t1.PersonId = t3.PersonId
 AND t3.Location = 'LOCATION3'
 AND ABS(DATEDIFF(day, t1.VISIIT_DATE, t3.VISIT_DATE)) <= 28
WHERE t1.PersonId = 12345
  AND t1.Location = 'LOCATION1';
user_50708790
Related