CODEWITHSUNDEEP
sqlnetezza
user3642531
user3642531

Reputation: 319

Flagging consecutive days in SQL

I have a table that looks something like this

PATIENT_ID    DATE_NOTED
1             01/01/2015 06:39
1             01/01/2015 17:15
1             01/02/2015 04:30
2             01/01/2015 07:34
2             01/03/2015 18:16

The dates in the table represent the times wheezing was recorded in the patient's record. I'm trying to flag patients who have at least one instance of at least two consecutive days of wheezing recorded, so a result table would look something like this:

PATIENT_ID    CONSECUTIVE
1             Yes
2             No

Patient 1 had wheezing recorded on 01/01/2015 and 01/02/2015, so they would be flagged yes. Patient 2 had wheezing recorded on 01/01/2015 and 01/03/2015, so they would be flagged no. I played around with row_number() a bit, but I can't figure out how to code this. I'm using Netezza.

Upvotes: 0

Views: 383

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I think your best bet is lag() or lead() and aggregation:

select patient_id,
       max(case when date_trunc('day', date_noted) = date_trunc('day', prev_date_noted)
                then 1 else 0
           end) as IsConsecutiveFlag
from (select patient_id, date_noted,
             lag(date_noted) over (partition by patient_id order by date_noted) as prev_date_noted
      from t
     ) t
group by patient_id;

This produces a 0/1 value. You can actually substitute 'Yes' and 'No' to get the words if you prefer.

Upvotes: 1

Langosta
Langosta

Reputation: 497

I'm not sure about Netezza, but on SQL Server I'd do something like this.

SELECT DISTINCT 
PATIENT_ID
,CASE WHEN OT2.PATIENT_ID IS NULL THEN 'No' ELSE 'Yes' END AS CONSECUTIVE
FROM OBSERVATIONTABLE OT1

LEFT OUTER JOIN OBSERVATIONTABLE OT2
ON OT2.PATIENT_ID = OT1.PATIENT_ID
AND CONVERT(DATE, OT1.DATE_NOTED) = DATEADD(DAY, -1, CONVERT(DATE, OT2.DATE_NOTED))

I'm joining the table on itself but instead of joining on the same date, I'm trying to join on the day before. If the join is successful, then the joined table's patID won't be null, so we write a case statement to express that.

There are probably more efficient ways to do this, I would think. I might have some syntax errors since I freehanded this, but I hope you can get the idea of the logic I'm trying to implement.

Upvotes: 0

Related Questions