Toad World® Forums

Toad, Teradata, "following" script and still another question please.


#1

I have a script, Script 1 below, that works but needs modification to account for another column value.

I have modified it, Script 2 below, but now I get an error message:

“The user cannot perform that operation on dates”

The modification is the addition of the

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

Can anyone point me in the right direction please?

Thanks

WHP

SCRIPT 1

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

FROM ar_asrh_dm_etl_beacon.Flg_ER_Algo1a2)

WITH DATA;

SCRIPT 2
/*The lines above utilize a calculation of interval between dates in 2 consecutive rows,

but only consider SvcPlace_id for 1 row. They should include a check of the SvcPlace_id for the following row.*/

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

/*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 31

and “NextSvcPlace_id” = 38

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

FROM ar_asrh_dm_etl_beacon.Flg_ER_Algo1a2)

WITH DATA;


#2

I think I got it, I had to eliminate the date subtraction.

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

Thanks

WHP