CODEWITHSUNDEEP
postgresqlself-join
Ya Guy Godzilla
Ya Guy Godzilla

Reputation: 107

POSTGRESQL Comparing a time overlap across rows

I'm creating a query in our EHR. I am looking for patients that have accounts that are within 48 hours of each other to make sure they aren't coming back in with the same symptoms.

to work with i have the following columns in two tables

visit table

'patnum' which is the unique number attached to each visit
'mr_num' which is the unique number attached to each patient

.

er_log table

'patnum' which is the unique number attached to each visit
arrivaldt and arrivaltime which i will unite in a timestamp and is when the patient presented themselves
dischargedate and dischargetime which I will unite in a timestamp and is when the patient was discharged

1.what are some methods for comparing the arrivaltime and dischargetime across all of an mr_num's accounts and finding those with less than 48 hours of difference?

thanks!

edit: my explanation may be a little vague example pt with MR num '334455' is a frequent flyer at the ER. she has come in 25 times in the last 7 months. each of those visits has a unique patnum, an arrivaldate and a discharge date. I am trying to find if any of her visits happened within 48 hours of each other by comparing one visits date with every other visits arrival date. query will report those visits that are within 48hours of each other.

Upvotes: 0

Views: 374

Answers (2)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

I'm assuming that dischargetime and arrivaltime are of type timestamp to simplify the code, but you can always do the conversion on your own.

Previous answer

Use INNER JOIN to connect with table containing your times. Assuming that a patient can be discharged only after he presented himself, you can subtract arrivaltime from dischargetime to receive result in INTERVAL type and get back only those rows where they differ by maximum 2 days, which is 48 hours.

SELECT 
  v.mr_num, v.patnum
FROM 
  visit v
  INNER JOIN er_log e USING ( patnum )
WHERE 
  v.mr_num = ?
  AND e.dischargetime - e.arrivaltime < interval '2 days'

To get some deeper understanding you could look into SQL Fiddle I've created.

Current answer

Changes in code after comment and edited question. This will scan all visits for a particular patient and return those that happened within 2 days from the end of any of previous visits. You may want to change logic a bit with the line where the comparison takes place, to better suit your need

AND e1.dischargetime - e2.arrivaltime < interval '2 days'

and decide whether you base your times on arrivaltime or dischargetime for the current visit being compared and all the visits that are in a lookup.

SELECT 
  v.mr_num, v.patnum
FROM 
  visit v1
  INNER JOIN er_log e1 USING ( patnum )
WHERE 
  v.mr_num = ?
  AND EXISTS (
    SELECT 1
    FROM visit v2
    INNER JOIN er_log e2 USING ( patnum )
    WHERE 
      v1.mr_num = v2.mr_num 
      AND v1.patnum IS DISTINCT FROM v2.patnum
      AND e1.dischargetime - e2.arrivaltime < interval '2 days'
  )

Upvotes: 1

JefferMC
JefferMC

Reputation: 715

What you will need to do is do TWO inner joins to the ER_LOG table. I'm going to borrow the previous answer and extend it:

SELECT 
  v1.mr_num, v1.patnum, v2.patnum,
 (e1.dischargetime - e2.arrivaltime) as elapsed
FROM 
  visit v1
  INNER JOIN er_log e1 on (v1.patnum = e1.patnum)
  INNER JOIN visit v2 on (v1.mr_num = v2.mr_num and v1.patnum <> v2.patnum)
  INNER JOIN er_log e2 on (v2.patnum = e2.patnum)
WHERE 
  v1.mr_num = ?
  AND e1.dischargetime - e2.arrivaltime < interval '2 days'

You must join against the er_log table twice to get the two different visits so that you can compare times. You must also join against visit again to establish visits for the same patient (mr_num), and you may exclude the identical visit by making sure the patnum values are different.

Upvotes: 0

Related Questions