Posted by Laphan on 03/08/06 23:44

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

[Back to original message]