I have one more question I hope someone can help provide a solution.
I am using this “Following” script below.
After I have created my final dataset I am getting records flagged as having an ER visit within 30 days when actually the follwowing record is another admit.
There are three differences between and admit and an er visit, an admit has a Stay_id that is populated, not (-2) and the admit has a claimCategory_id 4 and also has SvcPlace_id = 36, the ER visit is (-2), 3, and 38 respectively.
Here are some examples marked good and bad at the end of each record with an ER Visit Date:
Claim_id Mem_id DOS or Admit discharge ER Visit Date Day Diff 30DyERVisitFLG Stay_id SvcPlace_id ClaimCategory_id
4770414 969 6/2/2010 6/9/2010 6/10/2010 1 1 5599259 36 4 Bad
4770413 969 6/10/2010 6/16/2010 0 5410699 36 4
3398951 778 4/9/2010 4/10/2010 8/15/2010 127 0 2950938 36 4 Good
5812333 778 8/15/2010 1/1/0001 0 0 -2 38 3
9798296 1234 11/23/2010 11/24/2010 11/25/2010 1 1 11188548 36 4 Bad
8823029 1234 11/25/2010 11/29/2010 0 9985979 36 4
8823050 2223 12/1/2010 12/6/2010 12/20/2010 14 1 9981093 36 4 Good
9484443 2223 12/20/2010 1/1/0001 0 0 -2 38 3
Here is the script that get me to this point:
CREATE TABLE ar_asrh_dm_etl_beacon.Flg_ER_Algo1a3 AS
(SELECT Claim_id, Mem_id, Dte_SvcFrom_id AS “DOS or 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 Date”,
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 Stay_id <> -2 and "Day Diff" BETWEEN 0 and 31
THEN 1 ELSE 0
END AS "30DyERVisitFLG", Dte_SvcFrom_id, Dte_Admit_id, Dte_Disch_id,
Stay_id, SvcPlace_id, ClaimCategory_id, Dx_Cd, Dx_Dl, PatStat_dl, PatStat_id,
ProvRend_LastName, ProvRend_Id
FROM ar_asrh_dm_etl_beacon.Flg_ER_Algo1a2)
WITH DATA;
Any thoughts on this would be appreciated.
Thanks
WHP