Toad World® Forums

"FOLLOWING" statement with Flag necessary for more than occurance


#1

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