Post reply

Top Sales per Month

  • January 31, 2005 at 6:42 am

    #102371

    Help required.....

    I have a sales table with following intBookingID (PK), intStaffID (FK), strResNo, dtCaptured.

    Bookings are input on a daily basis. What I need is to return the top Seller for each month with a count of their sales for that month e.g.

    intStaffID       Count(*)       Month

    20                   170              02

    23                    139             03

    17                     220             04

    .........

     

    Any help appreciated.....thanks in advance.....

     

     

  • January 31, 2005 at 2:42 pm

    #539806

    There's no easy way to do this, that I'm aware of, so it someone has a way, I'd love to hear it. I'm guessing a lot of people would take the data, put it into temp table, and then aggregate from there.

     

    Here's a method that works, though, using subqueries on your query line.

    The primary drawback to it, is in the event of a tie in sales, only one person's name is going to come up.

    I used this table structure as a testbed.

    create table #test

    (idid int, sumsum int, monthmonth int)

    insert into #test values (1,1,12)

    insert into #test values (2,1,11)

    insert into #test values (2,1,12)

    insert into #test values (3,1,11)

    insert into #test values (4,1,12)

    insert into #test values (5,1,10)

    insert into #test values (3,1,10)

    insert into #test values (2,1,10)

    insert into #test values (3,1,10)

    insert into #test values (2,1,12)

    insert into #test values (4,1,11)

    insert into #test values (2,1,11)

    insert into #test values (2,1,11)

    insert into #test values (1,1,11)

    insert into #test values (1,1,12)

    insert into #test values (1,1,12)

    insert into #test values (1,1,12)

    insert into #test values (1,1,12)

    Now, this query below will take each month, and give you the top # of sales, along with the sales id of the person who obtained it.

    select distinct monthmonth as 'MONTH',

    (select top 1 count(sumsum) from #test t where t.monthmonth = r.monthmonth group by t.monthmonth, t.idid order by count(sumsum)desc) as 'SALES',

    (select top 1 idid from #test t1 where t1.monthmonth = r.monthmonth group by t1.monthmonth, t1.idid  order by count(sumsum)desc) as 'SALES ID' from #test r

     



    David W. Clary
    MCSD
    Data Miner 49er
    Sr. Database Administrator, Ceiva Logic

  • January 31, 2005 at 10:10 pm

    #539849

    "The primary drawback to it, is in the event of a tie in sales, only one person's name is going to come up."

     

    You can use WITH TIES to allow for this.

    Try

    SELECT TOP 1 WITH TIES COUNT(...

     

    Hope this helps.

    David Bruce

  • February 1, 2005 at 4:43 am

    #539889

    All works a treat.....

     

    thanks David and David.......

  • February 1, 2005 at 9:37 am

    #539966

    Doh!

    With Ties.

    /wonders why I forgot that.

     

    Good catch D.



    David W. Clary
    MCSD
    Data Miner 49er
    Sr. Database Administrator, Ceiva Logic

  • February 1, 2005 at 2:47 pm

    #540037

    David C, I am an Access developer first, and WITH TIES is automatic for it.  I paid close attention when I found out that SQL Server was different.

    David B>

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply