How to correctly calculate cash balances?

Question

SQL

There are tables for purchase, sale (date, quantity, price, item id)
How to find out the balance on the date?

The rest in a quantitative form is considered simply bought - sold, and is easily displayed in the context of the nomenclature.

But what about the balance in cash?

The quantity of goods in the last batches * their price, with the exception of an incomplete batch, is calculated there in proportion to the quantity.

How to implement this in SQL
Or at least logic

I would like batch accounting, but only the option with a multitude of sql queries in the

Select nomenclarure_id loop on receipt comes to mind.

Further, in the loop for each nomenclature, find the amount of expense
then make a request to select the last batches from the income to the balance, add all the batches in the cycle except the last one, multiplying the quantity by the sum,

the last batch, because it may not be a whole count in proportion to the remainder.

It turns out the number of SQL queries at least = the number of nomenclature * 2. I would like it to be less ...

A small shop has 100 goods, this is already 200 queries. We'll have to fence Ajax, so as not to fly out by timeout

Answer 1

Hm .. and what is the incomprehensibility?
If we are talking about batch accounting - that is. in each sale there is a reference to a specific batch of purchases - then actually subtracting from the selection of purchases before the required date the selection of sales before the same date through a bunch of batches - we get the balances for each specific batch. The sum of the products of the balances in each batch by the purchase price - and will give the sum of the balances.
If the accounting is based on the average, then there can be several interpretations:
- the global average for all batches of income before the required date is multiplied by the quantitative balance (this is simpler)
- we put it on FIFO and then first exclude the reset "batches" (according to the chronology of receipt) and then for the remaining consider the average