Hi.
I have a problem with my data that is causing the results of my script below
to produce inaccurate results.
In some instances “by Mem_id” I have a record where the next following record is
a visit record which is what I am looking for but the visit occured in between the Admit & Discharge dates and I need to eveluate the next subsequent record to it.
(FYI, This is simply a visit that occured in the hospital during the admit and I am looking
for post discharge visits within 14 days of the discharge).
Currently I have:
WHEN “Office Visit Date” BETWEEN Dte_Admit_id AND Dte_Disch_id
THEN NULL
But this is only part of what I need.
Is there some way to modify my logic here to to do something like:
WHEN “Office Visit Date” BETWEEN Dte_Admit_id AND Dte_Disch_id then ignore that date and look at the next record chronologically “by Mem_id” and flag accordingly,
Maybe a Next_NextSvcPlace_id = 36 AND Stay_id <> -2 AND “Day Diff” BETWEEN 0 and 15 AND NEXT “NextSvcPlace_id” = 37
THEN 0.
Any thoughts would be appreciated, thank you.
WHP
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;