With this query:
SELECT SUM(qty) AS sumQty,
cnetprodid
FROM quoteitem
WHERE ordered = 1
AND sageSOPOrderReturnLineID IS NOT NULL
AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
AND cnetprodid LIKE 'S%'
AND price > 10
AND created > DATEADD(MONTH, -3, GETDATE())
GROUP BY cnetprodid
ORDER BY SUM(qty) DESC
I get one row back per product, the top row has 294 as sumQty
and 'S10381902' as the cnetprodid
Enter this query:
SELECT SUM(qty) AS sumQty,
p.ProdID,
p.CatID
FROM quoteitem
INNER JOIN cds_prod AS p
ON p.ProdID COLLATE Latin1_General_CI_AS = cnetprodid
INNER JOIN cds_atr AS a
ON a.ProdID COLLATE Latin1_General_CI_AS = p.ProdID
INNER JOIN lan_cat_sections AS c
ON c.attID = a.atrID
OR ( LEN(ISNULL(c.attID, '')) = 0
AND c.cat_id LIKE p.CatID + '%' )
AND ( ( c.[include] = 0
AND a.valID NOT IN (SELECT Value
FROM Split(c.valID, ',')) )
OR ( c.[include] = 1
AND a.valID IN (SELECT Value
FROM Split(c.valID, ',')) ) )
WHERE ordered = 1
AND sageSOPOrderReturnLineID IS NOT NULL
AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
AND cnetprodid LIKE 'S%'
AND price > 10
AND created > DATEADD(MONTH, -3, GETDATE())
GROUP BY p.ProdID,
p.CatID,
a.prodID
HAVING SUM(qty) > 10
ORDER BY SUM(qty) DESC
I get one row per product back but now the row with 'S10381902' as the ProdID
column has a figure of 8820 for sumQty
.
All I want is a number for how many products have been sold in the last quarter, not interested in products that have sold less than 10 units, hence the HAVING
part.
The COLLATE
is there because the tables come from different databases; one is in SQL_Latin1_General_CP1_CI_AS
and another database is in Latin1_General_CI_AS
.
Fiddle: http://sqlfiddle.com/#!3/ce1ed/4