[RESOLVED] calculate benefits

[Mustaphi]

hello vbforums
In my sales and inventory application, I have these3 tables
I’m stuck at this point:
I want to calculate the benefits but I can’t find a way to do that.
Here are my tables:


As you can notice in this sample, the sum of benefit of these two items is 11 Dh
Milk = 4 * 2 = 8 Dh
Sugar 3 *1 = 3 Dh
Please help me with the appropriate query to calculate this benefit.
I'm using Sqlite3 and RC5
thank you all

[Zvoni]

1) there is a mistake in your second table. Pur_total for sugar should read 30
2) untested
select sum(s.sal_qty*(s.sal_price-p.pur_price)) as Benefit from sales_tbl as s
inner join purchase_tbl as p
on s.prd_id=p.prd_id

don‘t forget to reduce stock_qty after sales

[Mustaphi]

StrSql = "StrSql = "select sum(s.sal_qty * (s.sal_price - p.pur_price)) as Benefit from sales_tbl as s " & _
" inner join purchase_tbl  as p" & _
"  on s.prd_id=p.prd_id"

thank you very much sir but

Debug.Print Rs!Benefit

is not giving correct output

[Zvoni]

And what output is it giving?
it‘s always nice to know what‘s expected and what you get

[Mustaphi]

And what output is it giving?
it‘s always nice to know what‘s expected and what you get

thank you sir

the output is expected to be 11 as illustrated in post 1
this query is having the output 11. but I'm waiting for your confirmation before closing the post.

StrSql = "StrSql = "select sum(s.sal_price  - (s.sal_qty * p.pur_price)) as Benefit from sales_tbl as s " & _
" inner join purchase_tbl  as p" & _
"  on s.prd_id=p.prd_id"

thank you

[Zvoni]

There is no confirmation, since it is the most simple math combined with basic joins
i don’t need to test such simple stuff

[Schmidt]

What I don't understand (for any such calculations to make any sense) is:
- why there is no additional "Unit"-column
in the "Stock", "Purchase" and "Sales" tables. (or alternatively a "price_per_kg" column)

E.g.
- sugar-quantities could be measured (bought and sold) in "g", "pounds", "kg" - whatever.
- and for "fluids" like "milk" the quantities could be measured in "liters", "milli-liters" or alternatively in "weight-units" as well...

A quantity-column without a reference to a measurement-unit does not make any sense
(unless everything you "buy" is later on sold "in the same units")

E.g. if you sell 3 single packs of 500g sugar to a given customer -
but bought them in 5 cartons (10kg each) -
what entries will your purchase and sales-table list?

That's IMO the main-problem you have to address first,
before you can apply any reliable "profits"-math on those tables.
(and BTW, the two Pur_total and Sal_total columns are superfluous in their respective tables)

Olaf

[Mustaphi]

thank you Olaf

unless everything you "buy" is later on sold "in the same units"

that's exactly what is hapenning.
The application uses barcode scanner in the purchase and sale process.
A barecode of a pack of 500g sugar is different from a pack of 1 kilo
Similarely a barecode of one liter limonade is different from 2 liters.
thus the quantity in quantity column can refer to 500g , 1k, 1 litter or 2 liters depending on the barecode.

(and BTW, the two Pur_total and Sal_total columns are superfluous in their respective tables)

Pur_total column serves to calculate the total expenses
Sal_total column serves to calculate the total incomes
Furthermore, in my last code I have used this column to calculate the profits.
thanks

[Schmidt]

Pur_total column serves to calculate the total expenses
Sal_total column serves to calculate the total incomes

As said, these two columns should not exist in the table-definition.
They can always be derived "on the fly" by specifying a select like e.g.:

Select *, pur_price*pur_qty As pur_total From purchase_tbl

Which is a much more reliable way to reflect "a derived column" -
...because otherwise, errors like in the ScreenShot of your first posting creep in:
- where you have a wrong entry in pur_total for prd_id=2
.. (which should be 3*10=30 instead of your current 50)

Don't introduce "redundancies" like that without a good reason...

Furthermore, in my last code I have used this column to calculate the profits.

As said, this "last-column" should be a "dynamically derived" one (as shown in the blue SQL above) -
that's perhaps the reason, why you got different results,
compared to the math-expression Zvoni has posted -
(which looks good to me, whereas your expression in #5 does not).

HTH

Olaf

[Zvoni]

I didn‘t even look at his SQL.
olaf‘s right: that sql doesn’t make a lick of sense. He‘s summing up the difference of a price to a value.
But: not my problem if people don‘t use provided solutions.

On a sidenote, i disagree the total-columns being superfluous.
instead of filling them from the frontend i‘d rather create them as computed columns which sqlite supports.
As for the „use“ of them: yes, it depends on the business the database and logic is representing, but i know for a fact, that you need such columns if you are forced to calculate with TLC-Prices instead of usual purchase/sales-prices (TLC=total landed costs -> purchase price, freight charge, custom fees etc.)
Such columns are also of better use in accounting.
Nevermind that you need such columns when you have to reverse calculate average prices due to stock difference

[Mustaphi]

Olaf AND Zvoni
Million thanks