TOAD, Teradata, Not Getting Reliable Results As With Previous Data Series

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.

Look at MemID 106, Claim_IDs 65098113 and 69909150. To populate “Office Visit Date,” your partition is ordered by Mem_ID and SvcFrom. The problem with this is that the records can be in different orders based on only those two columns since you have multiple records with the same SvcFrom value. You need a “tie-breaker.” Without knowing a whole lot about your data, I would suggest that Office Visit Date column should be written as so:

MIN (SvcFrom) over (partition BY Mem_id ORDER BY Mem_id, SvcFrom, SvcTo
ROWS between 1 following and 1 following) AS “Office Visit Date”

Adding SvcTo to the Order By should get the records in the correct order from what I see in the Excel file. The other derived columns would need a similar fix.

Message was edited by: itlnstln

Hi itlnstln.

Terrific, I see exactly what you are saying.

I will try this solution and let you know.

Interestingly enough I did not receive an e-mail with your response, Hmmm?

Anyway, thank you.

WHP

Yep, that’s got it itlnstln.
Thank you!

Awesome! Glad it worked.