Tricky query

From: Rob <rob(at)obsidian(dot)co(dot)za>
To: PostgreSQL Server <postgres(at)obsidian(dot)co(dot)za>, <pgsql-novice(at)postgresql(dot)org>
Subject: Tricky query
Date: 2002-04-30 13:51:15
Message-ID: Pine.LNX.4.33L2.0204300942510.3208-100000@Genesis
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Henshall, Stuart - WCP 2002-04-30 16:35:19 Re: Help for incremental backup
Previous Message Lewis Bergman 2002-04-30 13:27:07 ALTER TABLE table DROP CONSTRAINT problem