Toad World® Forums

Consolidating Table Updates


#1

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;