0

Need help!

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!
91c25328627391cebcbd5f7bb9d3259d

2
Contributors
1
Reply
14
Views
4 Years
Discussion Span
Last Post by LastMitch
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.