Tricky query - Mailing list pgsql-novice

From Rob
Subject Tricky query
Date
Msg-id Pine.LNX.4.33L2.0204300942510.3208-100000@Genesis
Whole thread Raw
List pgsql-novice
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


pgsql-novice by date:

Previous
From: "Josh Berkus"
Date:
Subject: Re: Denormalization question, history+ current
Next
From: AarniRuuhimäki / Megative Tmi / KYMI.com
Date:
Subject: Dump version mismatch