Обсуждение: Tricky query

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

Try using the CASE construct:
CASE WHEN expr THEN expr [...] ELSE expr END
http://www.postgresql.org/idocs/index.php?functions-conditional.html

> ==========================
> Date: Tue, 30 Apr 2002 09:51:15 -0400 (EDT)
> From: Rob <rob@obsidian.co.za>
> To: PostgreSQL Server <postgres@obsidian.co.za>, <pgsql-novice@postgresql.org>
> Subject: [NOVICE] Tricky query
> ==========================
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that
> your
> message can get through to the mailing list cleanly

Simple sum of two queries

1) value/1.14 WHERE  vatInclusive  is true
2)value WHERE vatInclusive is false

should give the correct answer.

Rob wrote:

> Hi all
>
> 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.
>
> --
> Rob
>

> Simple sum of two queries
>
> 1) value/1.14 WHERE  vatInclusive  is true
> 2)value WHERE vatInclusive is false
>
> should give the correct answer.
>
> Rob wrote:
>
> > Hi all
> >
> > 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.

Even more straightforward:

SELECT barcode,
       stock_count * ( CASE WHEN vat_inclusive THEN cost_price / 1.14
                            ELSE cost_price
                            END
                      ) AS value
FROM   Latest_stock_count
NATURAL JOIN Products;


By the way, Rob, in your original query, there's no need to use the "WHERE
l.barcode = p.barcode" -- the NATURAL JOIN takes care of the joining fields.
You'd need to use that WHERE only if you didn't use NATURAL JOIN, but did
this as "FROM table1, table2." If you want the join to be more explicit
(it's easier for humans to understand what's working sometimes this way),
you can say "FROM table1 JOIN tabl2 USING (barcode)."

HTH.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant