Hi.
I felt like I had this pretty well perfected running against a previous database.
Now I am running against new series of data (different client) and there are some differences in how this data is coded but nothing that leads me to believe I shouldn't expect to get reliable results as I did with previous data series.
For some reason this query is producing correct output and in some cases the output is incorrect.
I may be missing the obvious so I thought a second set of eyes might help.
I have attached an excel spreadsheet with output and highlighted in red the values that are populating incorrectly given my intent with the “FOLLOWING” script below.
Example:
For Mem_id 106 where the Claim_id = 45348012 & 46567622 the incorrect "Office Visit Date" and "NextSvcPlace_id" are populating
Another example of this is Mem_id 1414 where the Claim_id = 77628132 & 77629201
& 77652208 & 77637796
Thanks for any insight.
WHP
CREATE TABLE Sandbox.Flg_OV_Algo1a1 AS
(SELECT Claim_id, Mem_id, SvcFrom AS "DOS or Admit", SvcTo AS discharge,
MIN (SvcFrom) over (partition BY Mem_id ORDER BY Mem_id, SvcFrom
ROWS between 1 following and 1 following) AS "Office Visit Date",
MIN (SvcFrom) over (partition BY Mem_id ORDER BY Mem_id, SvcFrom
ROWS between 1 following and 1 following) - SvcTo 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, SvcFrom
ROWS between 1 following and 1 following) AS "NextSvcPlace_id",
/*With that available, the CASE statement can be altered as below. */
CASE
WHEN SvcPlace_id IN (1, 8, 46) AND Admission <> -2 AND "Day Diff" BETWEEN 0 and 15 AND "NextSvcPlace_id" NOT IN(1, 8, 46)
THEN 0
WHEN PatStat_id IN (2, 3, 4, 5, 7, 10, 11, 15, 16, 17, 18, 19, 20, 21, 22, 28, 29) and SvcPlace_id IN (1, 8, 46)
THEN 0
WHEN SvcPlace_id IN (1, 8, 46) AND Admission <> -2 AND "NextSvcPlace_id" IN (1, 8, 46)
THEN NULL
WHEN SvcPlace_id NOT IN (1, 8, 46) and Admission = -2
THEN NULL
WHEN "Office Visit Date" BETWEEN SvcFrom AND SvcTo
THEN NULL
WHEN "Day Diff" < 0
THEN NULL
ELSE 1
END AS "14DyOfficeVisitFLG", Admission, Visit, SvcFrom, SvcTo, SvcPlace_id, SvcPlace_cd, "SvcPlace Description",
SvcType_id, SvcType_cd, SvcType_dl, ETOS_id, ETOS_cd, ETOS_dl, ClaimType_id, "Claim Type Description",
Rev_id, Rev_cd, "Rev Code Description", Proc_Code_id, Proc_Code_cd, "Proc CPT Description",
BETOS_ClaimLine_id, BillingProvider_id, PerformingProvider_id, ProvVer_id, Prov_id, ProvType_id,
ProvSpec_id, Prov_LastName, ProvSpec_cd, "ProvSpc Description", PatStat_id, "PatStat Description",
OfficeVisitInd_id, OfficeVisitInd_cd, OfficeVisitInd_ds, ConsistentDRG_id, Dx_Prim_id, Dx_Prim_cd,
Dx_Prim_dl, CCS_Level1_Prim_id
FROM Sandbox.Flg_OV_Algo1a)
WITH DATA;
UPDATE Sandbox.Flg_OV_Algo1a1
SET "Office Visit Date" = null
WHERE Admission = -2;
UPDATE Sandbox.Flg_OV_Algo1a1
SET "Day Diff" = ''
WHERE Admission = -2;
UPDATE Sandbox.Flg_OV_Algo1a1
SET "Day Diff" = ''
WHERE "Day Diff" < 0;
OUTPUT:
See attached please.