I have a data-set like:
org_id patient_id date
313 455259 2018-01-07
313 455259 2018-04-09
679 455259 2018-07-08
780 455259 2018-09-06
This patient was at the same org in the first two claims. After that, they changed orgs. How I find the first org they change to? For example, this is the output I am looking for -
org patient new_org
313 455259 679
CodePudding user response:
I mean the query below can help you:
with data as (
-- get records with previous value of org_id
select
patient_id,
org_id,
date,
lag(org_id) over (partition by patient_id order by date) prev_org_id
from tbl
)
-- select records where org_id changed
select * from data
where org_id != prev_org_id and prev_org_id is not null;
PostgreSQL window functions fiddle
CodePudding user response:
This solution works for multiple patients.
With A As (
Select patient_id, org_id, Lead(org_id) Over (Partition by patient_id Order by date, org_id) As new_org, Row_Number() Over (Partition by patient_id Order by date, org_id) As sort
From Tbl
),
B As (
Select patient_id, org_id, new_org, Row_Number() Over (Partition by patient_id Order by sort) As num
From A
Where org_id<>new_org
)
Select patient_id, org_id, new_org
From B
Where num=1
Output:
patient_id | org_id | new_org
___________|________|_________
455259 | 313 | 679
CodePudding user response:
This should work for all edge cases.
WITH data AS (
SELECT *,
LAG(org_id, 1, org_id) OVER (PARTITION BY patient_id ORDER BY date) AS prev_org,
(org_id <> LAG(org_id, 1, org_id) OVER (PARTITION BY patient_id ORDER BY date)) :: int AS flag
FROM tb1
),
agg_data AS (
SELECT *,
MIN(CASE flag WHEN 1 THEN date END) OVER (PARTITION BY patient_id) AS min_date
FROM data
)
SELECT prev_org AS org, patient_id AS patient, org_id AS new_org
FROM agg_data
WHERE date = min_date;
See Demo