My problem is this - I am trying to construct a database/query in MS Access to calculate the appropriate reimbursement amounts for medical procedures performed by doctors. While most of it is "straightforward", meaning I can do it within a single record, One particular billing practice is not.
When a patient receives multiple surgeries in a single day, they are subject to a "Multiple surgery reduction" - The Surgeries are ranked in order from most to least expensive, and then the first is reimbursed at 100%, the second at 50%, and the third and subsequent surgeries at 25%. There are no indicators within the individual records that multiple surgeries were performed.
I am trying to construct a query that will, whenever a procedure is a surgery, check if any other surgeries were performed that day, rank that procedure in the list of surgeries, and apply the appropriate reduction.
I was thinking that a subquery could do it, but my Procedure Table currently has 580,000 records and is growing, so execution time is a big factor. I have created a minimal example with a naïve pricing query at: http://sqlfiddle.com/#!9/7254e/9. Obviously, all the data is fictitious, due to HIPPA laws. The real situation is obviously quite more complicated than that, but it suffices to show the specific problem I'm tangling with.
If anyone could give me a method (it doesn't necessarily have to be in a single query) that would be great. Thanks for the Read!
Thank you for providing example data but would have been better to put it as tables directly in the question.
What is needed in the data is a daily sequence ID for surgery procedures for each patient. Might be able to generate with DCount() domain aggregate in query but it will probably be so slow with large dataset as to be useless. In which case your only recourse may be VBA solution to populate sequence field in table.
Consider:
Query1: PatientsFees
SELECT FeeList.ID AS FeeID, ProcList.ID AS ProcID, ProcList.Pat_Name,
ProcList.Procedure, FeeList.Price, FeeList.Surgery,
DateSerial(Year([Date_Service]),Month([Date_Service]),Day([Date_Service])) AS DateSvs,
Format([Price],"00000") & "_" & Format([ProcList].[ID],"00000") AS PriceID
FROM ProcList INNER JOIN FeeList ON ProcList.Procedure = FeeList.Procedure;
Query2:
SELECT PatientsFees.Pat_Name, PatientsFees.DateSvs, PatientsFees.PriceID,
PatientsFees.Procedure, PatientsFees.Price, PatientsFees.Surgery,
DCount("*","PatientsFees","Pat_Name='" & [PatientsFees].[Pat_Name]
& "' AND Surgery = 1 AND DateSvs=#"
& [PatientsFees].[DateSvs] & "# AND PriceID>'" & [PriceID] & "'")+
IIf([Surgery]=1,1,0) AS SurSeq,
[Price]*Switch([Surgery]=0 Or [SurSeq]=1,1,[SurSeq]=2,0.5,True,0.25) AS Reimb
FROM PatientsFees
ORDER BY PatientsFees.Pat_Name, PatientsFees.DateSvs, PatientsFees.PriceID DESC;