Top Sales per Month

Question

Top Sales per Month

Warren Haley

Grasshopper

Points: 17
More actions
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.....

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

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

dclary Old Hand Points: 321 More actions · Answer 1

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 (1,1,11)

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

dnbruce Valued Member Points: 71 More actions · Answer 2

"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

Warren Haley Grasshopper Points: 17 More actions · Answer 3

All works a treat.....

thanks David and David.......

dclary Old Hand Points: 321 More actions · Answer 4

Doh!

With Ties.

/wonders why I forgot that.

Good catch D.

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

dnbruce Valued Member Points: 71 More actions · Answer 5

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>