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;