I am trying to create an ASP Web page which lists the most successful agents (salesmen) for each previous month.
February 2002 - Simon Cooper - $12345
January
Requires Free Membership to View
2002 - Joe Fletcher - $18371etc...
I already know how to find the sales totals for each agent, however I am a little stuck as to how to list only the highest one, and more importantly - how to list them by month.
Any ideas would be MUCH appreciated!
I'll illustrate with an example. Suppose we have a table of orders like this:
create table Orders ( Ordered datetime, SalesRep varchar(30), Amount money );That table is populated with this SQL:
insert into Orders values ('2002-01-14','Joe',4000);
insert into Orders values ('2002-01-20','Roger',300);
insert into Orders values ('2002-01-21','Susan',6000);
insert into Orders values ('2002-01-25','Susan',1400);
insert into Orders values ('2002-02-03','Roger',300);
insert into Orders values ('2002-02-05','Susan',18000);
insert into Orders values ('2002-02-12','Joe',5000);
insert into Orders values ('2002-02-17','Joe',20000);
insert into Orders values ('2002-02-23','Joe',2900);
insert into Orders values ('2002-02-24','Susan',100);
We know we can get a summary of orders placed by month for each sales rep with SQL like this:
select substring(convert(varchar,Ordered,120),1,7) Month,
SalesRep,
sum(Amount) Amount
from Orders
group by substring(convert(varchar,Ordered,120),1,7),
SalesRep;
The SUBSTRING and CONVERT functions are MS SQL Server functions that serve merely to extract the year and month from the Ordered date. In Oracle, I might use the TO_CHAR and TRUNC functions to accomplish the same thing. The results look something like this:MONTH SALESREP AMOUNT --------- -------- --------- 2002-01 Joe 4000.00 2002-01 Roger 300.00 2002-01 Susan 7400.00 2002-02 Joe 27900.00 2002-02 Roger 300.00 2002-02 Susan 18100.00We are interested in selecting the highest performing sales representative for each month. From the results, we see that Susan and Joe are the highest performers for January and February respectively. So how do we select only those rows? The question we wish to answer is, "For each month, who is the sales rep with the highest total sales?" But in order to phrase this in terms more appropriate for SQL, the question might be, "For each month, who are the sales reps with total sales equal to the total sales of the rep with the highest total sales for the month." This may sound confusing, but it works for SQL. We'll start by creating a view to make our final SQL a bit more readable. Here it is:
create view OrderSummary as
select substring(convert(varchar,Ordered,120),1,7) Month,
SalesRep,
sum(Amount) Amount
from Orders
group by substring(convert(varchar,Ordered,120),1,7),
SalesRep;
Then, we write our SQL in terms of the aforementioned SQL friendly question, selecting all of the rows where the monthly sales are equal to the maximum monthly sales for a given month. It looks like this:
select *
from OrderSummary t
where Amount = ( select max(Amount) from OrderSummary
where Month = t.Month )
order by t.Month;
The results are just what we'd expect:MONTH SALESREP AMOUNT --------- -------- --------- 2002-01 Susan 7400.00 2002-02 Joe 27900.00Incidentally, this SQL has the fortunate quality of being able to handle ties nicely. For example, if Roger were to tie with Susan in January, we would probably want both of them to show up. Let's give Roger another order to demonstrate the situation and run the SQL again.
insert into Orders values ('2002-01-30','Roger',7100);
MONTH SALESREP AMOUNT
--------- -------- ---------
2002-01 Roger 7400.00
2002-01 Susan 7400.00
2002-02 Joe 27900.00
This was first published in March 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation