July 3, 2007 at 9:27 am
Hi guys,
I’m trying to figure out a question where I am supposed to report the Number of orders for each Salesperson in the year 2004. I also need to return a “Bonus” value for each salesperson based on this Number of orders value. Let’s say that every salesperson who has made over 100 orders will get a bonus. Here was my first pass at a solution (each row in the SalesOrderHeader is one order)
SELECT SalesPersonID, COUNT(*) AS NumOrders, Bonus=
CASE
WHEN NumOrders > 100 THEN ‘Bonus’
ELSE ‘No Bonus’
END
FROM Sales.SalesOrderHeader
WHERE year(OrderDate)=2004 AND SalesPersonID IS NOT NULL
GROUP BY SalesPersonID, Bonus
This does not compile, with the error that the NumOrders column is invalid. So I’m assuming you can’t use a case statement with the result of an aggregate function from the same row? If so, what would be the best way to solve this? So far, all I can think of is creating a temp table with SalespersonID and NumOrders, then writing a second select statement that pulls those as scalar values to calculate the bonus. This ended up working fine, but I have no idea if it is the best way to go about it. Can you help?
Thank you!
Bobby
July 3, 2007 at 9:38 am
Maybe I haven't had my coffee yet, but you should be able to just replace NumOrders with COUNT(*)...
CASE
WHEN COUNT(*) > 100 THEN 'Bonus'
ELSE 'No Bonus'
END
Also drop off the second column in the group by clause.
July 3, 2007 at 9:42 am
Try breaking the problem into two steps:
1. A SQL to determine NumOrders using the aggregation;
2. A second SQL to use the returned results from [1] (in the same vein as a derived table) using the CASE statements;
Paul
July 3, 2007 at 9:52 am
SELECT
SalesPersonID,
CASE
WHEN NumOrders > 100 THEN 'Bonus'
ELSE ' No Bonus'
END AS Bonus
FROM
(
SELECT SalesPersonID,
COUNT(*) AS NumOrders
FROM Sales.SalesOrderHeader
WHERE OrderDate >= '2004-01-01'
AND OrderDate < '2005-01-01'
AND SalesPersonID IS NOT NULL
GROUP BY SalesPersonID
) AS d
ORDER
BY SalesPersonID
N 56°04'39.16"
E 12°55'05.25"
July 3, 2007 at 5:10 pm
Thanks guys, I have a reply for each of you:
Aaron:
Now what if I had multiple ranges of bonuses to assign based on multiple ranges of Total orders. For example:
SELECT SalesPersonID, COUNT(*) AS NumOrders, Bonus=
CASE
WHEN COUNT(*) > 99 THEN ‘25%’
WHEN COUNT(*) > 79 and COUNT(*) < 100 THEN ‘20%’
WHEN COUNT(*) > 59 and COUNT(*) < 80 THEN ‘15%’
WHEN COUNT(*) > 39 and COUNT(*) < 60 THEN ‘10%’
WHEN COUNT(*) > 19 and COUNT(*) < 40 THEN ‘5%’
ELSE ‘No Bonus’
END
FROM Sales.SalesOrderHeader
WHERE year(OrderDate)=2004 AND SalesPersonID IS NOT NULL
GROUP BY SalesPersonID, Bonus
In this case do we have no other choice but to make the server compute the same COUNT(*) value ten times for each row returned? It seems to me like there must be a more efficient way...
Paul:
That's the best I could figure out, and it works fine. I'm just trying to see if there isn't a better way, than to have to do it two select statements? (Maybe there isn't)
Peter:
That seems to be basically what Paul was saying, or at least around the same order of performance. Similar comment applies... Looks like there doesnt' seem to be a better way of doing this (2 select statements)...
July 4, 2007 at 1:47 am
It is called a derived table. The table aliased do all the work, the outer select only formats it.
N 56°04'39.16"
E 12°55'05.25"
July 5, 2007 at 9:26 am
Guys, thank you for your help, much appreciated.
Bobby
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy