As you can see, I'm working with a crosstab report that will display the product's "Total Sales" or "Quantity Sold" on a particular year in a monthly basis. Now my problem is, I can't figure it out how I can display the monthly total sales or quantity sold for a particular product on a grid. I've tried using WHILE statement on SQL but still I wasn't able to get the ideal output.
Here is my SQL Query:
DECLARE @Month int SET @Month =1 WHILE (@Month <= 12) BEGIN SELECT P.Name AS 'Product Name' ,SUM(SOIL.QuantityDisplay) AS 'Qty' ,SUM(UnitPrice) AS 'Amount' FROM BASE_Product P INNER JOIN SO_SalesOrderInvoice_Line SOIL ON SOIL.ProductId = P.ProductId WHERE DATEPART(m, SOIL.CreatedDateTime) = @Month AND DATEPART(YY, SOIL.CreatedDateTime) = 2013 GROUP BY P.Name, SOIL.QuantityDisplay, SOIL.UnitPrice SET @Month += 1 END
Please enlighten me what's wrong!