Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group