Hi Folks,
I am attempting to estimate a proposed benefit plan limitation of service units accruals for a government non-profit health care system. Certain procedures have certain proposed limitations as to how much service units an individual can receive in a given period of time. I am attempting to return any rows that exceed that proposed limitation and having a hard time constructing the query to do so. Basically, if I group the data by unique individual, procedure, post date, service date, and agency (all fields in ascending order) I would need to somehow create a running sum field to sum the units for that grouping of uci and procedure while keeping an ascending order of the post date, service date, and agency. And then I would need to only show/display those records where the proposed limit has been reached. Is there a easy way to do this with a query? Any help would be greatly appreciated, an example is below:
Name Procedure Posting Service Date Location Units Sum
John Q. Citizen ASSMT 3/7/09 1/5/09 Summit 1.75 1.75
John Q. Citizen ASSMT 3/7/09 1/6/09 Zenith 2.55 4.3
John Q. Citizen ASSMT 3/15/09 1/2/09 Summit 3.15 7.45
John Q. Citizen ASSMT 3/15/09 1/5/09 Secor 2.00 9.45
John Q. Citizen ASSMT 3/25/09 1/9/09 Lucas 7.75 17.2
If the proposed unit cap for 'ASSMT' is a sum of 7 units, then rows three thru five from above would be returned to indicate that this client received more than the proposed unit cap for 'ASSMT'. Notice that row 3 is only 0.45 above the cap so it is a partial. Idealy, sum of dollars would also be displayed by the name/procedure/posting/service date/location grouping so that I can determine how much value in dollars the overage in service units totals.
Any help would be greatly appreciate.
Thank you,
Joe
I am attempting to estimate a proposed benefit plan limitation of service units accruals for a government non-profit health care system. Certain procedures have certain proposed limitations as to how much service units an individual can receive in a given period of time. I am attempting to return any rows that exceed that proposed limitation and having a hard time constructing the query to do so. Basically, if I group the data by unique individual, procedure, post date, service date, and agency (all fields in ascending order) I would need to somehow create a running sum field to sum the units for that grouping of uci and procedure while keeping an ascending order of the post date, service date, and agency. And then I would need to only show/display those records where the proposed limit has been reached. Is there a easy way to do this with a query? Any help would be greatly appreciated, an example is below:
Name Procedure Posting Service Date Location Units Sum
John Q. Citizen ASSMT 3/7/09 1/5/09 Summit 1.75 1.75
John Q. Citizen ASSMT 3/7/09 1/6/09 Zenith 2.55 4.3
John Q. Citizen ASSMT 3/15/09 1/2/09 Summit 3.15 7.45
John Q. Citizen ASSMT 3/15/09 1/5/09 Secor 2.00 9.45
John Q. Citizen ASSMT 3/25/09 1/9/09 Lucas 7.75 17.2
If the proposed unit cap for 'ASSMT' is a sum of 7 units, then rows three thru five from above would be returned to indicate that this client received more than the proposed unit cap for 'ASSMT'. Notice that row 3 is only 0.45 above the cap so it is a partial. Idealy, sum of dollars would also be displayed by the name/procedure/posting/service date/location grouping so that I can determine how much value in dollars the overage in service units totals.
Any help would be greatly appreciate.
Thank you,
Joe
