I need some advice on something that should be simple but for the life of me I cannot get my mind wrapped around it. It is hand coded stuff. No browses and such.
I need to have a table that keeps a running balance of certain types of work hours.
Compensatory Time - This is perpetual. Earn/Use time
Vacation - Not perpetual. Resets January 1st every year
I have a seperate table to store the earned/used hours and the balance.
So far, I can get it to work while inserting records. I use a VIEW with Previous(TheView) to get the last record’s balance and adjust the balance upon insert. This approach works for that but….
What I cannot figure out is what if the user goes back in time and changes the hours earned on a past date? I want to adjust the balances from that past date forward to the last record. I want to avoid looping through every record because, in time, there could potentially be thousands of records and I want it to be as quick a process as possible. I have not been able to get the math to work.
I hope this makes sense.
Any guidance from the Master Jedi is appreciated!