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.

[ck9663]

Sorry, too lazy to read your code so I'll post something similar :)

Try doing something like this:

select * from 
(
select
rank = row_number() over (partition by month order by numberofsolditems desc)
month, numberofsolditems
from 
(your subquery) q) z
where rank <= 4
 

Happy Coding!!!

~~ CK

[Microblitz]

Care to elaborate?

[NeoPa]

What do you need help with?

Have you tried looking through it to see how you can apply these techniques to your situation?

[Microblitz]

Well I got this far last night but it doesnt work.

It produces a "Msg 8120, Level 16, State 1, Line 14
Column 'Products.Produ ctId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
"
WHich is being caused by the inclusion of the SELECT product.product sId

But the function being proposed is one which I don't fully understand.

Specifically these dot functions such as the ".ss" I can only assume is to identify specific variables. I've never seen them before in any of the code I've researched (Remember I'm only about 16 hours into programming this language).

I have a database which contains information about stock across multiple tables and the point is to locate the top 4 best selling items on a per month basis over the last 6 months then plot them on a barograph. So what I am trying to do is even more complex for me than I am showing here. I can handle the graphics part I just need to figureout how to do what in any normal programming language would be a nested loop.

DECLARE @StartDate        SMALLDATETIME
DECLARE @EndDate        SMALLDATETIME
 
SET @StartDate            = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE())-6,0))
SET @EndDate            = GETDATE()
 
 
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
 
SELECT * FROM
        (
                SELECT 
                    Products.ProductId,
                    CONVERT(INT, SUM(ManufacturingOrders.QuantityMade)) AS Quantity,
                    CONVERT(CHAR(10), 
                                LTRIM(STR(YEAR(ManufacturingOrders.ReleaseDate))) + '/' + 
                                LTRIM(STR(MONTH(ManufacturingOrders.ReleaseDate)))) AS ReleaseDate,
 
                    ROW_NUMBER()OVER (ORDER BY  ReleaseDate,Quantity DESC)AS RowNumber,
                    RANK() OVER (ORDER BY ReleaseDate) AS Rank
 
                FROM ManufacturingOrders
 
                    INNER JOIN Products ON ManufacturingOrders.Product = Products.Product
                    INNER JOIN Classifications ON Products.Classification = Classifications.Classification
 
                WHERE 
 
                    Classifications.ClassificationId = 'STOCK 332' 
                    AND    ManufacturingOrders.ReleaseDate BETWEEN @WhereDateStart 
                    AND @WhereDateEnd AND ManufacturingOrders.SystemType = 'C'
 
 
        Group By ReleaseDate,Quantity)ss
 
WHERE ss.RowNumber = ss.Rank or ss.RowNumber = ss.Rank+1
 

[Microblitz]

Conversely if I simplyfy it as much as possible and take CK9663's original code and simply insert what I think are the relevent fields producing this version;

SELECT * FROM  
( 
SELECT 
Products.ProductId,
CONVERT(INT, SUM(ManufacturingOrders.QuantityMade)) AS Quantity, -- calculate total sales
 
RANK = ROW_NUMBER() OVER (PARTITION BY MONTH ORDER BY Quantity DESC) 
MONTH, Quantity
 
FROM ManufacturingOrders
 
INNER JOIN Products ON ManufacturingOrders.Product = Products.Product
INNER JOIN Classifications ON Products.Classification = Classifications.Classification
 
(
Classifications.ClassificationId = 'stock item 443' 
AND    ManufacturingOrders.ReleaseDate BETWEEN @WhereDateStart 
AND @WhereDateEnd AND ManufacturingOrders.SystemType = 'C') q) z 
WHERE RANK <= 4 
 

It produces;
"Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'MONTH'."

since the only thing I changed in that line of code was the quantity variable, I'm somwhat puzzled as to why there is a syntax error. All of the commands in that line (Except "Quantity") are reserved words.

[NeoPa]

Let's take a bunch of steps backwards. I do not even see how your attempts match the suggestion, but as you say you're new to this.

A step at a time then, do you understand subqueries (Subqueries in SQL) in SQL?
There are two levels of subquery in the proposed solution but, although there is a sort of one in one of your examples, it doesn't appear you have what is suggested in the original.

[Microblitz]

Well I do now, though my guess work was reasonably accurate. It provides a seconed layer of filtering to the original query.
I guess the two levels of sub query is the (sub query here) and the final line in the original example.
"where rank <= 4 "

[NeoPa]

ck9663:

select * from 
(
select
rank = row_number() over (partition by month order by numberofsolditems desc)
month, numberofsolditems
from 
(your subquery) q) z
where rank <= 4

Pretty well yes. Let me explain :
In line #7 (your subquery) q specifies your query and the result set is renamed (ALIASed) to [q]. This is equivalent to (your subquery) AS q.

From there the next level query (named [z] now) would be lines #2 through #7.

(
SELECT [Rank] = ROW_NUMBER() OVER (PARTITION BY [Month] ORDER BY [NumberOfSoldItems] DESC)
       [Month]
     , [NumberOfSoldItems]
FROM   (your subquery) AS q) AS z

I think possibly an extra comma (,) may be required before [Month].

This implies that your query must provide data for [Month] and [NumberOfSoldIte ms].

Anyway, if we now call this [QueryZ] then we are left with :

SELECT *
FROM   (QueryZ) AS [z]
WHERE  [Rank] <= 4

Does that all start to make a little more sense?

[Microblitz]

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

The "z" routeine I can leave as is, but the 'q' query needs to specify the particulatr brand of product from a related search.

This is the relationship that needs to be parsed

FROM ManufacturingOrders 
 
INNER JOIN Products ON ManufacturingOrders.Product = Products.Product 
INNER JOIN Classifications ON Products.Classification = Classifications.Classification 

Which goes inside the query q brackets?
and continues with ;

Classifications.ClassificationId = 'stock item 443'  
AND    ManufacturingOrders.ReleaseDate BETWEEN @WhereDateStart  
AND @WhereDateEnd AND ManufacturingOrders.SystemType = 'C'

?

I'm not stupid but the syntax of this language is freakish in comparision with those that I have played with in the past. 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.. :)