Posted by Astra on 03/09/06 12:40

Hi Erland

The following script is sooooooooo close to being right, but it is only
bringing back the right Sums when the stock items have a sold value and an
ordered value not all, just sold or just ordered:

SELECT st.STOCKID,
sd.FULLDESCRIPTION,
sc.NAME,
sq.QUANTITYINSTOCK AS 'Qty In Stock Total',
Sum(CASE WHEN st.TRANSACTIONTYPE=8 THEN st.QUANTITY ELSE 0 END)
AS 'Qty Sold',
Sum(CASE WHEN st.TRANSACTIONTYPE=1 THEN st.QUANTITY ELSE 0 END)
AS 'Qty Ordered'
FROM STOCK s
INNER JOIN STOCKCATEGORIES sc ON sc.STOCKCATEGORYID = s.STOCKCATEGORYID
INNER JOIN STOCKDESCRIPTIONS sd ON sd.STOCKID = s.STOCKID AND
sd.LANGUAGEID='UK'
INNER JOIN STOCKQUANTITIES sq ON sq.STOCKID = s.STOCKID AND
sq.WAREHOUSEID='BC' AND
sq.QUANTITYINSTOCK > 0
INNER JOIN STOCKTRANSACTIONS st ON st.STOCKID = s.STOCKID AND
st.TRANSACTIONTYPE IN(1,8)
GROUP BY st.STOCKID, sd.FULLDESCRIPTION, sc.NAME, sq.QUANTITYINSTOCK

It's got to be down to the way the joins work so is there anyway round this?

Many thanks

Regards

Robbie


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns978112C11FF4Yazorman@127.0.0.1...
Laphan (info@SpamMeNot.co.uk) writes:
> This works in a fashion, but I need to sort of query the stocktransactions
> table again to get the sum of the st.QUANTITY table for
> st.TRANSACTIONTYPE=1
> so that I can have an extra select field of Sum(st.QUANTITY) AS 'Qty
> Ordered', ie transactions with transactiontype of 1 are sales orders and
> type 8 are invoices.

It sounds like you could just add one column to your SELECT list:

SUM(CASE st.TRANSACTIONTYPE WHEN 1 THEN st.QUANTITY ELSE 0 END)
AS "Qty Ordered"

By the way, use of single quotes to delimit column aliases is deprected
in SQL 2005.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]