473,378 Members | 1,449 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

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

Expand|Select|Wrap|Line Numbers
  1. DECLARE @StartDate        SMALLDATETIME -- Variables for the dashboard
  2. DECLARE @EndDate        SMALLDATETIME
  3.  
  4. SET @StartDate            = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE())-6,0)) -- get todays month minus 6
  5. SET @EndDate            = GETDATE() -- get todays date
  6.  
  7. ----
  8.  
  9. DECLARE @WhereDateStart        SMALLDATETIME
  10. DECLARE @WhereDateEnd        SMALLDATETIME
  11.  
  12. SET @WhereDateStart = CONVERT(SMALLDATETIME,'01/' + LTRIM(STR(MONTH(@StartDate))) + '/' + LTRIM(STR(YEAR(@StartDate))))
  13.  
  14. SET @WhereDateEnd = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0))-1
  15. -- we want each date/month minus six months for filter
  16.  
  17.      SELECT -- Set up columns and graph output.
  18.  
  19.         LEFT(Products.ProductId,14) AS ChartLabel,
  20.         Products.ProductId,
  21.         CONVERT(INT, SUM(ManufacturingOrders.QuantityMade)) AS QuantityMade 
  22.         ,
  23.         CONVERT(CHAR(10), 
  24.             LTRIM(STR(YEAR(ManufacturingOrders.ReleaseDate))) + '/' + 
  25.             LTRIM(STR(MONTH(ManufacturingOrders.ReleaseDate)))) AS ReleaseDate -- trim dates
  26.     FROM
  27.         ManufacturingOrders
  28.         INNER JOIN Products ON ManufacturingOrders.Product = Products.Product -- Get the product type
  29.         INNER JOIN Classifications ON Products.Classification = Classifications.Classification -- Get the Product number
  30.     WHERE
  31.  
  32.          Products.ProductId IN (
  33.  
  34.             SELECT  Products.ProductId
  35.  -- sales code
  36.             FROM       Products INNER JOIN Classifications ON Products.Classification = Classifications.Classification
  37.  
  38.             WHERE    Classifications.ClassificationId = 'BOOK'
  39.                                                                       )     
  40.  
  41.                                   AND    ManufacturingOrders.ReleaseDate BETWEEN @WhereDateStart AND @WhereDateEnd
  42.           AND       ManufacturingOrders.SystemType = 'B'
  43.                                   AND       QuantityMade > 0
  44.  
  45.     GROUP BY
  46.  
  47.         Products.ProductId
  48.         ,
  49.         YEAR(ManufacturingOrders.ReleaseDate)
  50.         ,
  51.         MONTH(ManufacturingOrders.ReleaseDate)
  52.     ORDER BY
  53.         ReleaseDate
  54.          ,
  55.          ProductId
  56.  
This produces ChartLabel,ProductId,QunatityMade,ReleaseDate columns.

but its everything for each month, I just want the top 4 for each month to be sent to the graph.
Aug 13 '10 #1

✓ answered by ck9663

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

Try doing something like this:

Expand|Select|Wrap|Line Numbers
  1. select * from 
  2. (
  3. select
  4. rank = row_number() over (partition by month order by numberofsolditems desc)
  5. month, numberofsolditems
  6. from 
  7. (your subquery) q) z
  8. where rank <= 4
  9.  
Happy Coding!!!

~~ CK

12 2946
ck9663
2,878 Expert 2GB
Sorry, too lazy to read your code so I'll post something similar :)

Try doing something like this:

Expand|Select|Wrap|Line Numbers
  1. select * from 
  2. (
  3. select
  4. rank = row_number() over (partition by month order by numberofsolditems desc)
  5. month, numberofsolditems
  6. from 
  7. (your subquery) q) z
  8. where rank <= 4
  9.  
Happy Coding!!!

~~ CK
Aug 13 '10 #2
Care to elaborate?
Aug 25 '10 #3
NeoPa
32,556 Expert Mod 16PB
What do you need help with?

Have you tried looking through it to see how you can apply these techniques to your situation?
Aug 25 '10 #4
Well I got this far last night but it doesnt work.

It produces a "Msg 8120, Level 16, State 1, Line 14
Column 'Products.ProductId' 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.productsId

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.


Expand|Select|Wrap|Line Numbers
  1. DECLARE @StartDate        SMALLDATETIME
  2. DECLARE @EndDate        SMALLDATETIME
  3.  
  4. SET @StartDate            = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE())-6,0))
  5. SET @EndDate            = GETDATE()
  6.  
  7.  
  8. DECLARE @WhereDateStart        SMALLDATETIME
  9. DECLARE @WhereDateEnd        SMALLDATETIME
  10.  
  11. SET @WhereDateStart = CONVERT(SMALLDATETIME,'01/' + LTRIM(STR(MONTH(@StartDate))) + '/' + LTRIM(STR(YEAR(@StartDate))))
  12. SET @WhereDateEnd = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0))-1
  13.  
  14. SELECT * FROM
  15.         (
  16.                 SELECT 
  17.                     Products.ProductId,
  18.                     CONVERT(INT, SUM(ManufacturingOrders.QuantityMade)) AS Quantity,
  19.                     CONVERT(CHAR(10), 
  20.                                 LTRIM(STR(YEAR(ManufacturingOrders.ReleaseDate))) + '/' + 
  21.                                 LTRIM(STR(MONTH(ManufacturingOrders.ReleaseDate)))) AS ReleaseDate,
  22.  
  23.                     ROW_NUMBER()OVER (ORDER BY  ReleaseDate,Quantity DESC)AS RowNumber,
  24.                     RANK() OVER (ORDER BY ReleaseDate) AS Rank
  25.  
  26.                 FROM ManufacturingOrders
  27.  
  28.                     INNER JOIN Products ON ManufacturingOrders.Product = Products.Product
  29.                     INNER JOIN Classifications ON Products.Classification = Classifications.Classification
  30.  
  31.                 WHERE 
  32.  
  33.                     Classifications.ClassificationId = 'STOCK 332' 
  34.                     AND    ManufacturingOrders.ReleaseDate BETWEEN @WhereDateStart 
  35.                     AND @WhereDateEnd AND ManufacturingOrders.SystemType = 'C'
  36.  
  37.  
  38.         Group By ReleaseDate,Quantity)ss
  39.  
  40. WHERE ss.RowNumber = ss.Rank or ss.RowNumber = ss.Rank+1
  41.  
Aug 26 '10 #5
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;

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM  
  2. SELECT 
  3. Products.ProductId,
  4. CONVERT(INT, SUM(ManufacturingOrders.QuantityMade)) AS Quantity, -- calculate total sales
  5.  
  6. RANK = ROW_NUMBER() OVER (PARTITION BY MONTH ORDER BY Quantity DESC) 
  7. MONTH, Quantity
  8.  
  9. FROM ManufacturingOrders
  10.  
  11. INNER JOIN Products ON ManufacturingOrders.Product = Products.Product
  12. INNER JOIN Classifications ON Products.Classification = Classifications.Classification
  13.  
  14. (
  15. Classifications.ClassificationId = 'stock item 443' 
  16. AND    ManufacturingOrders.ReleaseDate BETWEEN @WhereDateStart 
  17. AND @WhereDateEnd AND ManufacturingOrders.SystemType = 'C') q) z 
  18. WHERE RANK <= 4 
  19.  
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.
Aug 26 '10 #6
NeoPa
32,556 Expert Mod 16PB
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.
Aug 26 '10 #7
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 "
Aug 26 '10 #8
NeoPa
32,556 Expert Mod 16PB
ck9663:
Expand|Select|Wrap|Line Numbers
  1. select * from 
  2. (
  3. select
  4. rank = row_number() over (partition by month order by numberofsolditems desc)
  5. month, numberofsolditems
  6. from 
  7. (your subquery) q) z
  8. 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.
Expand|Select|Wrap|Line Numbers
  1. (
  2. SELECT [Rank] = ROW_NUMBER() OVER (PARTITION BY [Month] ORDER BY [NumberOfSoldItems] DESC)
  3.        [Month]
  4.      , [NumberOfSoldItems]
  5. 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 [NumberOfSoldItems].

Anyway, if we now call this [QueryZ] then we are left with :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   (QueryZ) AS [z]
  3. WHERE  [Rank] <= 4
Does that all start to make a little more sense?
Aug 26 '10 #9
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
Expand|Select|Wrap|Line Numbers
  1. FROM ManufacturingOrders 
  2.  
  3. INNER JOIN Products ON ManufacturingOrders.Product = Products.Product 
  4. INNER JOIN Classifications ON Products.Classification = Classifications.Classification 
Which goes inside the query q brackets?
and continues with ;
Expand|Select|Wrap|Line Numbers
  1. Classifications.ClassificationId = 'stock item 443'  
  2. AND    ManufacturingOrders.ReleaseDate BETWEEN @WhereDateStart  
  3. 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.. :)
Aug 27 '10 #10
NeoPa
32,556 Expert Mod 16PB
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.
Aug 27 '10 #11
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
Expand|Select|Wrap|Line Numbers
  1. DECLARE @Classification bigint
  2. DECLARE @StartDate smalldatetime
  3. DECLARE @EndDate smalldatetime
  4. DECLARE @MonthEndDate smalldatetime
  5. DECLARE @TempDate smalldatetime
  6.  
  7. SET @Classification = (SELECT Classification FROM Classifications WHERE Classifications.ClassificationId  = 'STOCKITEM')
  8. SET @StartDate = CONVERT(smalldatetime,'01 ' + RIGHT(CONVERT(nvarchar,getdate(),106),8))
  9. SET @EndDate = DATEADD(d,-1,@StartDate)
  10. SET @StartDate = DATEADD(m,-6,@StartDate)
  11.  
  12. CREATE TABLE #DETAILS
  13. (
  14.     SalesYear    int,
  15.     SalesMonth    int,
  16.     ProductId    nvarchar(100),
  17.     Quantity    decimal(17,5)
  18. )
  19.  
  20. SET @TempDate = @StartDate
  21.  
  22. WHILE @TempDate < @EndDate
  23. BEGIN
  24.     SET @MonthEndDate = DATEADD(d,-1,DATEADD(m,1,@TempDate))
  25.  
  26.  
  27.     INSERT INTO #DETAILS
  28.     (
  29.         SalesYear,
  30.         SalesMonth,
  31.         ProductId,
  32.         Quantity
  33.     )
  34.     SELECT TOP 4
  35.         YEAR(@TempDate),
  36.         DATEPART(m,@TempDate),
  37.         Products.ProductId,
  38.         SUM(SalesInvoiceItems.Quantity) AS QuantityInvoiced
  39.     FROM SalesInvoiceItems
  40.         INNER JOIN SalesInvoices ON SalesInvoiceItems.SalesInvoice = SalesInvoices.SalesInvoice
  41.             AND dbo.wfn_GetSimpleDate(SalesInvoices.EffectiveDate) BETWEEN @TempDate AND @MonthEndDate
  42.             AND SalesInvoices.SystemType = 'F'
  43.             AND SalesInvoices.SourceType = 'I'
  44.         INNER JOIN Products ON SalesInvoiceItems.Product = Products.Product
  45.             AND Products.Classification = @Classification
  46.     GROUP BY
  47.         Products.ProductId
  48.     ORDER BY
  49.         QuantityInvoiced DESC
  50.  
  51.  
  52.     SET @TempDate = DATEADD(m,1,@TempDate)
  53. END
  54.  
  55. SELECT *
  56. FROM #DETAILS
  57.  
  58. DROP TABLE #DETAILS
  59.  
Sep 24 '10 #12
The alternative code using the technique as explained above by ck9663 for comparison.
Expand|Select|Wrap|Line Numbers
  1. DECLARE @Classification bigint
  2. SET @Classification = (SELECT Classification FROM Classifications WHERE Classifications.ClassificationId  = 'STOCKITEM')
  3.  
  4. select * from  
  5.     select 
  6.         Dtbl.SalesYear
  7.         Dtbl.SalesMonth,
  8.         Dtbl.ProductId,
  9.         row_number() over (partition by SalesYear,SalesMonth order by QuantityInvoiced desc) AS Ranking,
  10.         Dtbl.QuantityInvoiced 
  11.     from  
  12.     (
  13.         SELECT
  14.             YEAR(SalesInvoices.EffectiveDate) AS SalesYear,
  15.             DATEPART(m,SalesInvoices.EffectiveDate) AS SalesMonth,
  16.             Products.ProductId,
  17.             SUM(SalesInvoiceItems.Quantity) AS QuantityInvoiced
  18.         FROM SalesInvoiceItems
  19.             INNER JOIN SalesInvoices ON SalesInvoiceItems.SalesInvoice = SalesInvoices.SalesInvoice
  20.                 AND dbo.wfn_GetSimpleDate(SalesInvoices.EffectiveDate) BETWEEN '01 Mar 2010' AND '31 Aug 2010'
  21.                 AND SalesInvoices.SystemType = 'F'
  22.                 AND SalesInvoices.SourceType = 'I'
  23.             INNER JOIN Products ON SalesInvoiceItems.Product = Products.Product
  24.                 AND Products.Classification = @Classification
  25.         GROUP BY
  26.             YEAR(SalesInvoices.EffectiveDate),
  27.             DATEPART(m,SalesInvoices.EffectiveDate),
  28.             Products.ProductId
  29.     ) AS Dtbl
  30. ) As DTbl2
  31. where Ranking <= 4 
Sep 24 '10 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

16
by: sandy | last post by:
Hi, Using Java script I am trying to create code where when you place in the start date it automatically calculates 6 months for the experations date. For example when I place 01/01/04 as the...
1
by: Finlay | last post by:
Hi Group I am designing a report that should show a meter reading for each month and the previous meter reading for the previous month. The months are text stored in a field tMonth. The...
9
by: Robin Tucker | last post by:
Hiya, I need to test "relative dates" in my program, such as "last six months" or "last 3 months" or "in the last week" etc. How can I do this with a DateTime structure? ie. If NodeDate...
3
by: remya1000 | last post by:
i'm using ASP with MSAccess as database. i have two buttons and two textbox in my page. when i press my first button (First month) i need to display the current month in one textbox and last one...
2
by: nsymiakakis | last post by:
Hello Everyone, I am almost completed with this database, the only thing left is to graph the results of a query. I built a parameter query that gives me totals for a date range that I enter. This...
3
by: Johan Mcgillicutty | last post by:
Could use some help on this one- Iím new to MS Access, and even newer to SQL. Iím building a database for clients of a treatment center, and need to keep track of when people are due for their...
5
by: rekhasc | last post by:
hi all............... in my proj i am using a mschart .... now what i want is i want to display the graph amount v/s months I did the graph which shows date v/s amount its working good... i used...
11
by: Harlin Seritt | last post by:
Is there a module that can pull str values for say the last 3 months? Something like: print lastMonths(3) Thanks
3
by: Supermansteel | last post by:
I have a Query/Report that I am running a Date Parameter so it only pulls in the information for the End of Month Results for that person. It runs the Avg for everyone for that month also. I was...
9
by: mikewin86 | last post by:
Hello , I would like to ask a question concerned with SQL Server 2000 query. I've got a table PurchaseDetails and I would like to query from it. I want all product records with last purchase ID....
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.