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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply