# Help with Running Balance Please

**URL:** <https://clarionhub.com/t/help-with-running-balance-please/3616>\
**Category:** questions\
**Tags:** handcode\
**Created:** [October 18, 2020, 10:04am UTC](https://clarionhub.com/t/help-with-running-balance-please/3616 "2020-10-18T10:04:19Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![donridley](https://clarionhub.com/user_avatar/clarionhub.com/donridley/32/1459_2.png) [@donridley](https://clarionhub.com/u/donridley)\
**Post date:** [October 18, 2020, 10:04am UTC](https://clarionhub.com/t/help-with-running-balance-please/3616/1 "2020-10-18T10:04:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![seanh](https://clarionhub.com/letter_avatar_proxy/v4/letter/s/4af34b/32.png) [@seanh](https://clarionhub.com/u/seanh)\
**Post date:** [October 18, 2020, 11:33am UTC](https://clarionhub.com/t/help-with-running-balance-please/3616/2 "2020-10-18T11:33:44Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![donridley](https://clarionhub.com/user_avatar/clarionhub.com/donridley/32/1459_2.png) [@donridley](https://clarionhub.com/u/donridley)\
**Post date:** [October 18, 2020, 11:42am UTC](https://clarionhub.com/t/help-with-running-balance-please/3616/3 "2020-10-18T11:42:29Z")

</div>

> [@seanh](#):
>
> This sounds similar to a General Ledger situation.

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.

---

<div class="post-metadata">

**Author:** ![CarlBarnes](https://clarionhub.com/user_avatar/clarionhub.com/carlbarnes/32/1859_2.png) [@CarlBarnes](https://clarionhub.com/u/CarlBarnes)\
**Post date:** [October 18, 2020, 6:01pm UTC](https://clarionhub.com/t/help-with-running-balance-please/3616/4 "2020-10-18T18:01:59Z")

</div>

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)

```

---

<div class="post-metadata">

**Author:** ![jslarve](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/e480ec/32.png) [@jslarve](https://clarionhub.com/u/jslarve)\
**Post date:** [October 19, 2020, 7:02am UTC](https://clarionhub.com/t/help-with-running-balance-please/3616/5 "2020-10-19T07:02:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![donridley](https://clarionhub.com/user_avatar/clarionhub.com/donridley/32/1459_2.png) [@donridley](https://clarionhub.com/u/donridley)\
**Post date:** [October 19, 2020, 12:09pm UTC](https://clarionhub.com/t/help-with-running-balance-please/3616/6 "2020-10-19T12:09:27Z")

</div>

Thank you Jeff!!! I greatly appreciate it!