CODEWITHSUNDEEP
sqlsql-servert-sql
jasonkkt
jasonkkt

Reputation: 53

SQL How to return All products sold in last 30 days Include non sellers

I am trying return a best sellers list for the last 30 days for a category ordered by sum quantity

If the category has 10 products and only 5 have sold I want to return all 10 products ordered by most sold.

Here's what I have so far

SELECT ROW_NUMBER() OVER
    (
    ORDER BY 
     ISNULL(SUM(B.quantity),0) desc
         )AS RowNumber
,
a.productID, 
ISNULL(SUM(B.quantity),0) AS countquantity,
a.product

FROM
products a
left join savedDetails b on a.productid =b.productid
left join Category d on d.Categorysec = a.CategoryID 

WHERE 
(a.pinstock='1' and a.BrandID = '7' and a.categoryid = '15' and (b.despatchdate >= DATEADD(DAY,-30,GETDATE()) OR b.despatchdate IS NULL))

GROUP BY 
a.productid,
a.CategoryID, 
a.product

Just need some help as this only returns products sold or never sold. If a product sold 32 days again it doesn't get returned.

Thanks

Upvotes: 0

Views: 883

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280351

Move this clause:

and (b.despatchdate >= DATEADD(DAY,-30,GETDATE())

To the first left join clause. Putting it in the where clause makes SQL Server evaluate values on actual rows, turning your left join into an inner join. (This also makes the IS NULL check redundant.)

So:

...
FROM
products a
left join savedDetails b on a.productid =b.productid
    and (b.despatchdate >= DATEADD(DAY,-30,GETDATE())
left join Category d on d.Categorysec = a.CategoryID 
WHERE 
(a.pinstock='1' and a.BrandID = '7' and a.categoryid = '15'
GROUP BY 
...

Upvotes: 5

Related Questions