I have a database being used to track and bill therapy units. I have a table that stores the pre-authorizations that tells me within a [start date] and [end date] I am limited with X amount of units (that is all the client's insurance and/or funding source will pay for within the time period). I also have about 4 different codes I can be authorized for which is also stored in this table - each code a separate pre-auth record.
Another table is where I enter in the type of therapy (service code) I've completed with the client. I put in the Service date, start time and end time and it will automatically give me the amount of units to bill (1 hour=1 unit). If I happen to choose a code or enter in a service date that does not match up with a client's pre-auth (Service date is between start date and end date and matches code from pre-auth table) I have a simple text box that says "No auth for this code or service date" thanks to an IIF statement in a query. I'd like to take this a step further and give me the remaining units for the pre-authorization left when it does find a match.
What is happening is when it finds the authorization, it's not taking into consideration all the services I've done for that time frame. For example: I have a pre-auth from 12/1/12 to 1/31/13 for code 90806 for 12 units. I served the client for that code a total of 10 units thus far. I can't figure out how to link my 4 service records dated 12/3/12 (2 units), 12/16/12 (4 units), 12/27/12 (2 units), and 1/3/13 (2 units) to add together and then subtract from my auth for 12 units. I know the dates are the key but I'm lost. I'm hoping I'm just overlooking a simple step - I'm a bit burned out.
I don't know SQL. If SQL is the only way, I'll give it a shot. Any help you can provide will be greatly appreciated.