Re: Tricky query

> 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