Hi.
I feel like there should be a more elegant way to perform some or all of these 4 updates, and probably somewhere in my “FOLLOWING” routine below rather than afterwards.
Any thoughts?
Thanks
WHP
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;
UPDATE ar_asrh_dm_etl_beacon.Flg_ER_Algo1a1
SET “ER Visit” = null
WHERE Stay_id = -2;
UPDATE ar_asrh_dm_etl_beacon.Flg_ER_Algo1a1
SET “Day Diff” = ‘’
WHERE Stay_id = -2;
UPDATE ar_asrh_dm_etl_beacon.Flg_ER_Algo1a1 /Need to set the flag to 0 WHERE “Day Diff” < 0 first!/
SET “30DyERVisitFLG” = 0
WHERE “Day Diff” < 0;
UPDATE ar_asrh_dm_etl_beacon.Flg_ER_Algo1a1
SET “Day Diff” = ‘’
WHERE “Day Diff” < 0;