Hi all
I've got a products table in my database that has the cost price of a
product and another field called vatInclusive that is simply a boolean
that tells me whether the cost price is vat inclusive or not. I also have
a table latest_stock_count that has a field stock_count that contains the
latest stock count.
I want to calculate the value of my stock holdings, which is simply
latest_stock_count.stock_count * products.cost_price. I have the
following query which does this
SELECT p.barcode, (l.stock_count * p.cost_price) AS value
FROM latest_stock_count AS l NATURAL JOIN products AS p
WHERE l.barcode = p.barcode;
The problem is that I always want the cost_price excluding sales tax
(known as VAT - which is 14%). So if vatInclusive is true, what I
actually want is cost_price/1.14, not cost_price.
So, to take an example, is product 12345 has a cost_price of 10 and a
stock_count of 100 and the cost_price is not vatInclusive then the stock
value = 1000 (100 * 10). However, if the cost price is vatInclusive, then
the stock_value is 877.19 (100 * (10/1.4))
Is there any way to do this in straight sql?
--
Rob
He who dies with the most toys ...
... still dies