Hi All

I know that I should supply the DDL for the tables I'm going to talk about,
but I'm not 100% on how to generate them just yet. Hopefully my question is
more a query methodology question than how the tables are constructed.

My first attempt at the query is as follows:

SELECT st.STOCKID, sd.FULLDESCRIPTION, sc.NAME, Sum(sq.QUANTITYINSTOCK) AS
'Qty In Stock Total', Sum(st.QUANTITY) AS 'Qty Sold'
FROM STOCK s, STOCKCATEGORIES sc, STOCKDESCRIPTIONS sd, STOCKQUANTITIES sq,
STOCKTRANSACTIONS st
WHERE sc.STOCKCATEGORYID = s.STOCKCATEGORYID AND st.STOCKID = s.STOCKID AND
sd.STOCKID = s.STOCKID
AND sq.STOCKID = s.STOCKID AND (sd.LANGUAGEID='UK') AND
(st.TRANSACTIONTYPE=8) AND
(sq.QUANTITYINSTOCK > 0)
GROUP BY st.STOCKID, sd.FULLDESCRIPTION, sc.NAME

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.

I basically need to get a report result of:

PRODUCT, DESCRIPTION, CATEGORY, CURRENT_STOCK_QTY, SALES_IN_PERIOD,
ORDERS_IN_PERIOD

Is there any pointers whatsoever you can give me to try and get this
double-double query to work?

Many thanks.

Rgds Laphan

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Apologies it works perfectly

It was me.

Many, many thanks.

Rgds 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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx