Graph of top four products each month for last 6 months?

[Microblitz]

Graph of top four products each month for last 6 months?
(Yeah I know I typ'od the title)

I need create a chart which displays the four highest selling products for each month in the last 6 months to be displayed in a report.

Ive managed to get all the products for each month for the last six months but im unsure how to filter so it displays only the top four for each month.

Ive done some programming but I thiinnk im going about this wrongly.

This is my code so far

DECLARE @StartDate        SMALLDATETIME -- Variables for the dashboard
DECLARE @EndDate        SMALLDATETIME
 
SET @StartDate            = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE())-6,0)) -- get todays month minus 6
SET @EndDate            = GETDATE() -- get todays date
 
----
 
DECLARE @WhereDateStart        SMALLDATETIME
DECLARE @WhereDateEnd        SMALLDATETIME
 
SET @WhereDateStart = CONVERT(SMALLDATETIME,'01/' + LTRIM(STR(MONTH(@StartDate))) + '/' + LTRIM(STR(YEAR(@StartDate))))
 
SET @WhereDateEnd = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0))-1
-- we want each date/month minus six months for filter
 
     SELECT -- Set up columns and graph output.
 
        LEFT(Products.ProductId,14) AS ChartLabel,
        Products.ProductId,
        CONVERT(INT, SUM(ManufacturingOrders.QuantityMade)) AS QuantityMade 
        ,
        CONVERT(CHAR(10), 
            LTRIM(STR(YEAR(ManufacturingOrders.ReleaseDate))) + '/' + 
            LTRIM(STR(MONTH(ManufacturingOrders.ReleaseDate)))) AS ReleaseDate -- trim dates
    FROM
        ManufacturingOrders
        INNER JOIN Products ON ManufacturingOrders.Product = Products.Product -- Get the product type
        INNER JOIN Classifications ON Products.Classification = Classifications.Classification -- Get the Product number
    WHERE
 
         Products.ProductId IN (
 
            SELECT  Products.ProductId
 -- sales code
            FROM       Products INNER JOIN Classifications ON Products.Classification = Classifications.Classification
 
            WHERE    Classifications.ClassificationId = 'BOOK'
                                                                      )     
 
                                  AND    ManufacturingOrders.ReleaseDate BETWEEN @WhereDateStart AND @WhereDateEnd
          AND       ManufacturingOrders.SystemType = 'B'
                                  AND       QuantityMade > 0
 
    GROUP BY
 
        Products.ProductId
        ,
        YEAR(ManufacturingOrders.ReleaseDate)
        ,
        MONTH(ManufacturingOrders.ReleaseDate)
    ORDER BY
        ReleaseDate
         ,
         ProductId
 

This produces ChartLabel,Prod uctId,QunatityM ade,ReleaseDate columns.

but its everything for each month, I just want the top 4 for each month to be sent to the graph.

[NeoPa]

MicroBlitz:
Yes that makes a lot more sense as soon as you mentioned the "AS" keyword.

I so agree. It's an optional keyword and I always use it myself as I feel clarity is so important, especially when reading someone-else's code.

Generally, I find that where subqueries become nested and complicated it is worthwhile to develop each in turn, starting from the innermost and proceeding to the overall. Clearly it's important to have a good understanding of what precisely is required at each level, but once the innermost one is developed and working you can think of it in its own little black box (within parentheses) and know that it does its job. The last SQL of post #9 for instance, is easy to work on if you know you can rely on (QueryZ) to do all that's required of it.

MicroBlitz:
I guess it doesnt help to only have been supplied with one book which is Sams teach yourself Microsoft SQL Server T-SQL.

Any suggestions for a quick rummage in amazon.co.uk for a better book than this. It is ... well erm.. brief.. :)

Not really, but I'm finding out all I need from the Context-Sensitive Help system. I find it is invaluable to me. I'm in a position not far different from yours in fact. I may well have a few years of Jet SQL experience, but T-SQL I'm picking up as I go along (after dipping in to T-SQL 2000 at a very basic level once or twice over the last few years). Not a day goes by at this time without my nose deeply buried in the Help system. It answers almost all of my questions well. If it weren't an electronic resource it would certainly be well thumbed by now, and I've only been even looking at this in the last month or two.

Anyway, good luck with your learning. Whichever way you find works best.

[Microblitz]

This is the final version I added to the code. It's not the same method as the suggestion above but I managed to get that way working after I got the report to function properly. If I can find the method detailed here I will post it for comparison - Microblitz

DECLARE @Classification bigint
DECLARE @StartDate smalldatetime
DECLARE @EndDate smalldatetime
DECLARE @MonthEndDate smalldatetime
DECLARE @TempDate smalldatetime
 
SET @Classification = (SELECT Classification FROM Classifications WHERE Classifications.ClassificationId  = 'STOCKITEM')
SET @StartDate = CONVERT(smalldatetime,'01 ' + RIGHT(CONVERT(nvarchar,getdate(),106),8))
SET @EndDate = DATEADD(d,-1,@StartDate)
SET @StartDate = DATEADD(m,-6,@StartDate)
 
CREATE TABLE #DETAILS
(
    SalesYear    int,
    SalesMonth    int,
    ProductId    nvarchar(100),
    Quantity    decimal(17,5)
)
 
SET @TempDate = @StartDate
 
WHILE @TempDate < @EndDate
BEGIN
    SET @MonthEndDate = DATEADD(d,-1,DATEADD(m,1,@TempDate))
 
 
    INSERT INTO #DETAILS
    (
        SalesYear,
        SalesMonth,
        ProductId,
        Quantity
    )
    SELECT TOP 4
        YEAR(@TempDate),
        DATEPART(m,@TempDate),
        Products.ProductId,
        SUM(SalesInvoiceItems.Quantity) AS QuantityInvoiced
    FROM SalesInvoiceItems
        INNER JOIN SalesInvoices ON SalesInvoiceItems.SalesInvoice = SalesInvoices.SalesInvoice
            AND dbo.wfn_GetSimpleDate(SalesInvoices.EffectiveDate) BETWEEN @TempDate AND @MonthEndDate
            AND SalesInvoices.SystemType = 'F'
            AND SalesInvoices.SourceType = 'I'
        INNER JOIN Products ON SalesInvoiceItems.Product = Products.Product
            AND Products.Classification = @Classification
    GROUP BY
        Products.ProductId
    ORDER BY
        QuantityInvoiced DESC
 
 
    SET @TempDate = DATEADD(m,1,@TempDate)
END
 
SELECT *
FROM #DETAILS
 
DROP TABLE #DETAILS
 

[Microblitz]

The alternative code using the technique as explained above by ck9663 for comparison.

DECLARE @Classification bigint
SET @Classification = (SELECT Classification FROM Classifications WHERE Classifications.ClassificationId  = 'STOCKITEM')
 
select * from  
( 
    select 
        Dtbl.SalesYear
        Dtbl.SalesMonth,
        Dtbl.ProductId,
        row_number() over (partition by SalesYear,SalesMonth order by QuantityInvoiced desc) AS Ranking,
        Dtbl.QuantityInvoiced 
    from  
    (
        SELECT
            YEAR(SalesInvoices.EffectiveDate) AS SalesYear,
            DATEPART(m,SalesInvoices.EffectiveDate) AS SalesMonth,
            Products.ProductId,
            SUM(SalesInvoiceItems.Quantity) AS QuantityInvoiced
        FROM SalesInvoiceItems
            INNER JOIN SalesInvoices ON SalesInvoiceItems.SalesInvoice = SalesInvoices.SalesInvoice
                AND dbo.wfn_GetSimpleDate(SalesInvoices.EffectiveDate) BETWEEN '01 Mar 2010' AND '31 Aug 2010'
                AND SalesInvoices.SystemType = 'F'
                AND SalesInvoices.SourceType = 'I'
            INNER JOIN Products ON SalesInvoiceItems.Product = Products.Product
                AND Products.Classification = @Classification
        GROUP BY
            YEAR(SalesInvoices.EffectiveDate),
            DATEPART(m,SalesInvoices.EffectiveDate),
            Products.ProductId
    ) AS Dtbl
) As DTbl2
where Ranking <= 4