Hi, is there a way to add a second or third CASE clause in a script like this below?
I have dichotomous outcome currently and need a third outcome that makes a field in certain records blank,
like CASE1 when “somthing” then 0, CASE2 when “something else” then ‘blank’ ELSE 1
specifically the second case would be
CASE WHEN SvcPlace_id = 37 and Stay_id = -2 THEN ‘’ ELSE 1
I can do an update after the fact but seems like this would be more elegant?
Thanks for any thoughts!
CREATE TABLE ar_asrh_dm_etl_beacon.Flg_OV_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 “Office 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",
/*I’m guessing I could obtain the next row’s SvcPlace_id as below. */
MIN (SvcPlace_id) over (partition BY Mem_id ORDER BY Mem_id, Dte_SvcFrom_id
ROWS between 1 following and 1 following) /- Dte_Disch_id/ AS “NextSvcPlace_id”,
/*With that available, the CASE statement can be altered as below. */
CASE WHEN SvcPlace_id = 36 and Stay_id <> -2 and “Day Diff” BETWEEN 0 and 15
and “NextSvcPlace_id” = 37
THEN 0 ELSE 1
END AS “14DyOfficeVisitFLG”, 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, MSDRG_id, ProvSpc_id, ProvSpc_ds