August 9, 2010 at 10:32 am
Hi,
I have a situation where I may need to use a cursor or while loop and trying with all my might not to. But I can’t come up with a better way to get the results I need. Here is what I need to accomplish.
I have a temp table of service dates for clients that we will need to bill to insurances companies. Each client has what we call authorizations that have a start and end date and number of units that tell us how many hours we can bill the insurance company.
I need to check each day in the temp table (because a client could have multiple days) to see which authorization we can bill as there could be overlapping authorizations. The problem is I need to keep track of how many units are left on each authorization while I’m processing the records.
Below is an example of what I need to accomplish:
Client A has 4 days of service we need to bill.
Day 1 has 28 Units
Day 2 has 28 Units
Day 3 has 24 Units
Day 4 has 24 Units
There are 2 authorizations available for Client A
Authorization 1 has 24 units left
Authorization 2 has 56 units left
So, I need something that will do this.
Check Day 1, can we use auth. 1, no, not enough units available. Can we use auth. 2, yes. Then subtract 28 units from auth. 2. Now auth. 2 has 28 units left.
Check Day 2, can we use auth. 1, no, not enough units available. Can we use auth. 2, yes. Subtract 28 units from auth. 2. Now auth. 2 has 0 units left.
Check Day 3, can we use auth. 1, yes. Subtract 24 units from auth. 1. Now auth. 1 has 0 units left.
Check Day 4, can we use auth. 1, no, not enough units available. Can we use auth. 2, no not enough units available. This day can't be billed.
When all processing is done, the authorizations for this client should have no more units left and we only had enough units for 3 of the 4 days.
Is there any way to accomplish this without using a while loop or cursor? If the answer is no, what is my best option a cursor or while loop?
I hope I’ve given enough information and this make sense, this is my first post!
Ty
August 9, 2010 at 10:49 am
You don't need a cursor or while loop, but you do need to provide quite a bit more information to get a coded example. Please see the link in my signature to see how to provide the structures and sample data.
Also, you refer a number of times to a temp table, but what is really needed is information about the permanent tables where the data is stored. And then how often is this run? Once a day? Do you have the flexibility to create additional tables or add columns to keep track of what billed hours have already be covered by what authorizations and what authorizations have been partially or completely used for what billed hours?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply