Toad World® Forums

Toad, Teradata, SQL, CASE Staements


#1

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!

WHP

Current script:

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

FROM ar_asrh_dm_etl_beacon.Flg_OV_Algo1a2)

WITH DATA;


#2

Hi I got it, thanks.

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",

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”,

CASE
WHEN SvcPlace_id = 36 AND Stay_id <> -2 AND “Day Diff” BETWEEN 0 and 15 AND “NextSvcPlace_id” = 37
THEN 0
WHEN PatStat_id IN (3, 8, 11, 12) and SvcPlace_id = 36
THEN 0
WHEN SvcPlace_id = 36 AND Stay_id <> -2 AND “NextSvcPlace_id” = 36
THEN NULL
WHEN SvcPlace_id = 37 and Stay_id = -2
THEN NULL
WHEN “Office Visit Date” BETWEEN Dte_Admit_id AND Dte_Disch_id
THEN NULL
WHEN “Day Diff” < 0
THEN NULL
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

FROM ar_asrh_dm_etl_beacon.Flg_OV_Algo1a2)

WITH DATA;