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)
SELECT P.Name AS 'Product Name'
    ,SUM(SOIL.QuantityDisplay) AS 'Qty'
    ,SUM(UnitPrice) AS 'Amount'
    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

Please enlighten me what's wrong!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.