I have the following tables:
Products
(Product_id,Product_Name,Product_Type,Cost_Price)
Sales
(SalesOrder_id, Product_id,Quantity, Sell_Price, Sale_Date)
I want to produce a query that provides most profitable products, per year,week,month,day and overall.
I can do these queries individually for example the top 5 profitable products per month
SELECT TOP 5 P.Product_Name,DATEPART(MM,S.SaleDate) AS MoNTH(S.Sell_Price P.Cost_Price) * S.Quantity AS Profit
FROM Products P INNER JOIN Sales S
ON S.Product_id = P.Product_id
GROUP BY P.Product_Name,DATEPART(MM,S.SaleDate)
Is there a way to produce an output with profitable products per week, year etc in one query?
Many aggregates in one query
Posted - 05/08/2012 : 20:45:21
