none
Top 5 sales EACH month RRS feed

  • Question

  • I have a table tblSales ( DollarAmount,DateSold, Barcode --- ) in SQL MSDE 2000.

    What I want is the Top 5 sales for EACH month:

    Month      TotalSales   Barcode 

    2006-05  Top01     Barcode01

    2006-05   Top02     Barcode02

    2006-05  Top03      Barcode03

    2006-05  Top04      Barcode04

    2006-05  Top05     Barcode05

    2006-04  Top11     Barcode11

    2006-04  Top12     Barcode12

    2006-04   Top13     Barcode13

    2006-04   Top14     Barcode14

    2006-04   Top15     Barcode15

    2006-03   Top21     Barcode21

    2006-03   Top22     Barcode22

    ----                    ---           ----

    ---                  ----             ---

    TopNN is SUM(DollarAmount).

    I created a table AAAA and use a while loop to insert the data into AAAA.

    What I did is:

      declare @StartDate and @EndDate, set @StartDate and @EndDate

    Delete AAAA

    Begin

    While (@EndDate<GetDate())

    Begin  

       Insert into AAAA(sales, Month, Barcode)

       Select TOP 5 SUM(DollarAmount) AS sales,

             LEFT(CONVERT(CHAR(8),DateSold,112),4)+'-'+RIGHT(LEFT(CONVERT(CHAR(8),DateSold,112),6),2) AS Month,

               Barcode

        from tblSales

       where something

       group by something

       order by sales1 DESC

       increase @StartDate and @EndDate by a month

    End

    Select * from AAAA

     

    It works fine. My question is: Can I get rid of table AAAA? 

    How can I use T-SQL only to get the correct sets?

    Thanks in advance.

    Long

    Wednesday, June 7, 2006 9:59 PM

Answers

  • In 2000, this is not tremendously easy when you have to include all the other processing in the query.  However, when you have the set you have, you can exclude rows something like this:

    -- I used your example table structure

    CREATE TABLE sales
    (
        monthSold   char(7),
        dollarAmount char(5),
        barcode     char(9)
    )
    INSERT INTO sales
    SELECT '2006-05','Top01','Barcode01'
    UNION ALL
    SELECT '2006-05','Top02','Barcode02'
    UNION ALL
    SELECT '2006-05','Top03','Barcode03'
    UNION ALL
    SELECT '2006-05','Top04','Barcode04'
    UNION ALL
    SELECT '2006-05','Top05','Barcode05'
    UNION ALL
    SELECT '2006-04','Top11','Barcode11'
    UNION ALL
    SELECT '2006-04','Top12','Barcode12'
    UNION ALL
    SELECT '2006-04','Top13','Barcode13'
    UNION ALL
    SELECT '2006-04','Top14','Barcode14'
    UNION all
    SELECT '2006-04','Top15','Barcode15'
    UNION ALL
    SELECT '2006-03','Top21','Barcode21'
    UNION ALL
    SELECT '2006-03','Top22','Barcode22'
    go

     

    SELECT *
    FROM   (SELECT *,(SELECT COUNT(*)   --this query adds the number
                                         FROM   sales AS s
                                         WHERE  s.dollarAmount <= sales.dollarAmount
                                                AND  s.monthSold = sales.monthSold) AS number
            FROM   sales) AS numbered
    WHERE   number <= 2 --i used 2 because you only included five a month. 

    In 2005, using CTE's and Ranking functions, it is far easier to do (and if you can use express edition, it is free too and has better tools :)

    Thursday, June 8, 2006 12:22 AM
    Moderator
  • As usual with SQl theres many ways to skin the cat... My initial response on reading the OP was to do it like this:

    select monthSold,  DollarAmount, barcode
      from sales a
     where barcode in
        (select top 5 barcode
           from sales b
          where a.monthSold = b.monthSold
          order by dollarAmount desc)
     order by monthSold, dollarAmount desc

    Assuming that barcode is unique for each row, otherwise using another primary key/unique column which you may not have given in your example table.

    Friday, June 9, 2006 11:36 PM

All replies

  • In 2000, this is not tremendously easy when you have to include all the other processing in the query.  However, when you have the set you have, you can exclude rows something like this:

    -- I used your example table structure

    CREATE TABLE sales
    (
        monthSold   char(7),
        dollarAmount char(5),
        barcode     char(9)
    )
    INSERT INTO sales
    SELECT '2006-05','Top01','Barcode01'
    UNION ALL
    SELECT '2006-05','Top02','Barcode02'
    UNION ALL
    SELECT '2006-05','Top03','Barcode03'
    UNION ALL
    SELECT '2006-05','Top04','Barcode04'
    UNION ALL
    SELECT '2006-05','Top05','Barcode05'
    UNION ALL
    SELECT '2006-04','Top11','Barcode11'
    UNION ALL
    SELECT '2006-04','Top12','Barcode12'
    UNION ALL
    SELECT '2006-04','Top13','Barcode13'
    UNION ALL
    SELECT '2006-04','Top14','Barcode14'
    UNION all
    SELECT '2006-04','Top15','Barcode15'
    UNION ALL
    SELECT '2006-03','Top21','Barcode21'
    UNION ALL
    SELECT '2006-03','Top22','Barcode22'
    go

     

    SELECT *
    FROM   (SELECT *,(SELECT COUNT(*)   --this query adds the number
                                         FROM   sales AS s
                                         WHERE  s.dollarAmount <= sales.dollarAmount
                                                AND  s.monthSold = sales.monthSold) AS number
            FROM   sales) AS numbered
    WHERE   number <= 2 --i used 2 because you only included five a month. 

    In 2005, using CTE's and Ranking functions, it is far easier to do (and if you can use express edition, it is free too and has better tools :)

    Thursday, June 8, 2006 12:22 AM
    Moderator
  • As usual with SQl theres many ways to skin the cat... My initial response on reading the OP was to do it like this:

    select monthSold,  DollarAmount, barcode
      from sales a
     where barcode in
        (select top 5 barcode
           from sales b
          where a.monthSold = b.monthSold
          order by dollarAmount desc)
     order by monthSold, dollarAmount desc

    Assuming that barcode is unique for each row, otherwise using another primary key/unique column which you may not have given in your example table.

    Friday, June 9, 2006 11:36 PM