Help with Running Balance Please

questions tips
1

Hello Everyone,

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.

For example:
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!

Don

2

SQL or TPS
The may be different.
But from your perplexed question I’d postulate that you’re keeping the wrong values.
What you really want to do is keep changes such that a simple adding up of the values gives the answer.

This sounds similar to a General Ledger situation. For some part you start at 0 and just add it up. If something changes in the middles, no problem, just add it up. For other stuff I’d keep monthly balances with start and end so if something changed you only need to adjust the monthlies, or the Financial year.

But the key is keeping the amount of change, not the total.
SQL just makes it easy by creating a view.

3

Exactly the correct analogy.

TPS backend for now.

I would like each table row to have the balance amount as of that record if possible. But, I’m not stuck on the idea.

4

Posting your file definitions might help understand.

If you want the running balance in the record it seems like the only way to handle changes is to cascade them across the multiple later records.

One idea would to have separate Year Summary records that store the running balance in a simple array so a cascade takes place in one record.

SummaryHours  FILE
OfficerID   LONG
Year	    SHORT   !YYYY
Jan1Date   LONG   !1/1/yyyy
OpenBalance  DECIMAL(7,2)
DayEndBalance  DECIMAL(7,2),DIM(366)
EndBalance  DECIMAL(7,2)
5

Here’s an example created in C55 or C6 using a queue. https://jssoftware.com/jssoftware/downloads/balance.zip

2 Likes
6

Thank you Jeff!!! I greatly appreciate it!

1 Like