Possibly simple query but I'm not good enough to fathom it!

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 LaphanPlease post your table DDLs, sample data & expected results. It is hard to
understand narratives when others have nothing else to verify what you are
saying.
For details, refer to: www.aspfaq.com/5006
Anith|||*untested*, based on your narrative,
SELECT st.STOCKID,
sd.FULLDESCRIPTION,
sc.NAME,
Sum(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.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|||Dear MarkC6000
Absolutely brilliant!!
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
<markc600@.hotmail.com> wrote in message
news:1141861441.222123.266230@.i40g2000cwc.googlegroups.com...
*untested*, based on your narrative,
SELECT st.STOCKID,
sd.FULLDESCRIPTION,
sc.NAME,
Sum(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.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|||Apologies it works perfectly
It was me.
Many, many thanks.
Rgds Robbie
<markc600@.hotmail.com> wrote in message
news:1141861441.222123.266230@.i40g2000cwc.googlegroups.com...
*untested*, based on your narrative,
SELECT st.STOCKID,
sd.FULLDESCRIPTION,
sc.NAME,
Sum(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.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