I am using this syntax to create a flag to flag a discharge where mem_id has ER visit within 30 days of discharge.
In some instances mem_id has more than one ER visit within the 30 day period post D/C and I would like to flag those as well.
Here is current script and below that some output examples.
CREATE TABLE ar_asrh_dm_etl_beacon.Flg_ER_Algo1a1 AS
(SELECT Claim_id, Mem_id, Dte_SvcFrom_id AS admit, Dte_Disch_id AS discharge,
MIN (Dte_SvcFrom_id) over (partition BY Mem_id ORDER BY Mem_id, Dte_SvcFrom_id
ROWS between 1 following and 1 following) AS “ER Visit”,
MIN (Dte_SvcFrom_id) over (partition BY Mem_id ORDER BY Mem_id, Dte_SvcFrom_id
ROWS between 1 following and 1 following )- Dte_Disch_id as "Day Diff",
CASE WHEN SvcPlace_id = 36 and
MIN (Dte_SvcFrom_id) over (partition BY Mem_id ORDER BY Mem_id, Dte_SvcFrom_id
ROWS between 1 following and 1 following ) - Dte_SvcFrom_id <= 30
THEN 1 ELSE 0
END AS "30DyERVisitFLG", Stay_id, SvcPlace_id, ClaimCategory_id, Dx_Cd, Dx_Dl,
PatStat_dl, PatStat_id, ProvRend_LastName, ProvRend_Id, Dte_Admit_id, Dte_Disch_id
FROM ar_asrh_dm_etl_beacon.Flg_ER_Algo1
GROUP BY Mem_id, Dte_SvcFrom_id, Claim_id, PatStat_dl, PatStat_id, admit, discharge,
ProvRend_id, ProvRend_LastName, Stay_id, SvcPlace_id, Dte_Admit_id, Dte_Disch_id,
ClaimCategory_id, Dx_Cd, Dx_Dl)
WITH DATA;
Claim_id Mem_id admit discharge ER Visit Day Diff 30DyERVisitFLG Stay_id SvcPlace_id
736446 991 2/2/2010 1/1/0001 0 0 -2 37
4017905 991 5/28/2010 1/1/0001 0 0 -2 37
5429489 991 7/12/2010 1/1/0001 0 0 -2 37
8885844 991 12/10/2010 1/1/0001 0 0 -2 37
10954559 991 1/19/2011 1/1/0001 0 0 -2 37
11124592 991 3/4/2011 1/1/0001 0 0 -2 37
11471785 991 3/5/2011 3/9/2011 3/28/2011 19 1 13000190 36
12010212 991 3/28/2011 1/1/0001 0 0 -2 37
12496578 991 3/29/2011 1/1/0001 0 0 -2 37
SvcPlace_id 36 = the Admit/Discharge
SvcPlace_id 37 & 38 = ER or Outpat Hospital
Discharged on the 9th, and that episode is flagged = 1 for the ER Visit on the 28th, 19 days.
Is there any way to increase the flag to 2 where there are 2 subsequent ER visits in the
30 day period
Thnk you for any thoughts
WHP