Content rating: Beginner, tips
Right click to download the setup and example code here.
I had a neat little request come across my desk yesterday (we'll abstract the details a bit for separation of company and blog). In essence, it was "find me all the salespeople this month that have met ALMOST all the requirements to get the Superdude bonus". They want to see who's meeting most, but not quite all, of their sales goals so that management can encourage them and send reminders.
Let's say we already have a convenient rollup table with all the salespeople's monthly summary info in it, so we can focus.
Table
Here's the rollup table:
create table salesRollup (salesGuyID int, SalesGuyName varchar(100), salesPeriodMonth tinyint, salesPeriodYear smallint, bridgesSold int, bridgeSales money, totalSales money )
Tip: By the way, I can't stress enough the importance of knowing and using your datatypes properly. Tinyint is only 1 byte, and holds integers from 0 to 255…why would I need int (4 bytes), or even smallint (2 bytes), to hold a month number? Save space where you can. (Now, I am using money instead of smallmoney, but hey, these guys are selling bridges and ladders to the moon….that's likely to rack up numbers higher than the $214,748 limit on smallmoney).
Requirements
Let's say that the requirements for the superdude bonus stretch over three consecutive months, and are:
-
Total sales of over $100,000 in month 1
-
Total sales of over $150,000 in month 2
-
Total sales of over $200,000 in month 3
-
Sold at least 2 bridges in month 2
-
Sold at least 4 bridges in month 3
-
Bridge sales should be more than $90,000 in month 3
Hey, we don't make the rules; we just get them from the business owners, and confirm them very, very well.
First step: Query to Meet All Sales Requirements
I know we're looking for those guys who ALMOST qualify, but "almost" is harder than "does", so let's start working on "does qualify", and go from there. Now, all this data is coming from one table, but we're going to self-join that table to itself to get the different month's information, like so:
select month1.*,month2.*,
month3.*
FROM salesRollup month1 — Month1 = January 2010
INNER JOIN salesRollup month2 ON month2.salesGuyID = month1.salesGuyID
AND month2.salesPeriodYear = month1.salesPeriodYear
AND month2.salesPeriodMonth = month1.salesPeriodMonth + 1 — Month2 = February 2010.
INNER JOIN salesRollup month3 ON month3.salesGuyID = month1.salesGuyID
AND month3.salesPeriodYear = month1.salesPeriodYear
AND month3.salesPeriodMonth = month1.salesPeriodMonth + 2 — Month3 = March 2010.
WHERE month1.salesPeriodMonth = 1
AND month1.salesPeriodYear = 2010
Tip: I don't actually advocate the use of SELECT *, but while we're developing – on our DEVELOPMENT box, not on our PRODUCTION box! – it's just shorthand.
See what we did there? "month1", "month2", and "month3" are aliases for our joined tables; this lets us join a table to itself, and makes it comprehensible. Now we can ACTUALLY think of month1, month2, and month3 as different tables, because the join for each one returns a different set of data. Next up, let's turn our requirements into TSQL:
-
Total sales of over $100,000 in month 1 => month1.totalSales > 100000
-
Total sales of over $150,000 in month 2 => month2.totalSales > 150000
-
Total sales of over $200,000 in month 3 => month3.totalSales > 200000
-
Sold at least 2 bridges in month 2 => month2.BridgesSold >=2
-
Sold at least 4 bridges in month 3 => month3.BridgesSold >=3
-
Bridge sales should be more than $90,000 in month 3 => month3.bridgeSales > 90000
Next: Change to "Almost" Met Sales Requirements
This query gets us the salespeople who DO qualify for the bonus. But what we need are the people who ALMOST qualify. Now, "almost" is going to be a business rule…do they want to see a report of people with enough total sales, but not enough bridge? People who qualify in month1 and 2, but not quite yet in 3? Find out what the business owners specifically mean by "almost", and then tailor your query. In our case, we've been asked for people who have month1 and 2 requirements, have 2 bridges sold in month 3, and are within 50% of their total and bridge sales in month 3. This changes the query thusly (ignoring the body of the query again, only WHERE clause changes):
… WHERE month1.salesPeriodMonth = 1 AND month1.salesPeriodYear = 2010 AND month1.totalSales > 100000 AND month2.totalSales > 150000 AND month3.totalSales >= CAST((.10 * 200000) AS int) AND month2.BridgesSold >=2 AND month3.BridgesSold >=2 AND month3.bridgeSales >= CAST((.10 * 90000) AS int)Different "Almost"
If the business had decided another tack for "almost" – like, anyone who has met month 1 and month 2 requirements, and at least one month 3 requirement – we'd have a different WHERE:
… WHERE month1.salesPeriodMonth = 1 AND month1.salesPeriodYear = 2010 AND month1.totalSales > 100000 AND month2.totalSales > 150000 AND month2.BridgesSold >=2 AND (month3.totalSales > 200000 OR month3.BridgesSold >=3 OR month3.bridgeSales > 90000)No "Did Meet" Guys
You get the idea. If we like, we can also eliminate those guys who DO meet all the criteria – after all, this is a report on potential bonus awardees:
… WHERE month1.salesPeriodMonth = 1 AND month1.salesPeriodYear = 2010 AND month1.totalSales > 100000 AND month2.totalSales > 150000 AND month2.BridgesSold >=2 AND (month3.totalSales > 200000 OR month3.BridgesSold >=3 OR month3.bridgeSales > 90000) AND NOT (month3.totalSales > 200000 AND month3.BridgesSold >=3 AND month3.bridgeSales > 90000)That last AND NOT says, if a guy also meets all thre month3 criteria, I don't want to see him.
Make it Readable
There's one more thing I'd like to do to this query, and that is to make it easily readable/usable. So let's change our SELECT statement (we'll ignore the FROM, JOIN, and WHERE clauses for now; they stay the same). We'll use this latest set of requirements…that is, all month1 and month2 criteria are met. So let's show them WHICH month3 criteria are being met:
select — Total sales case WHEN month3.totalSales > 200000 THEN 'YES' ELSE 'no' END AS TotalSalesMet_ThisMonth, — Bridges Sold case WHEN month3.BridgesSold >=3 THEN 'YES' ELSE 'no' END AS BridgesSoldMet_ThisMonth, — Bridge sales case WHEN month3.bridgeSales > 90000 THEN 'YES' ELSE 'no' END AS BridgeSalesMet_ThisMonth, month3.salesGuyID, month3.SalesGuyName, month3.salesPeriodMonth, month3.salesPeriodYear, month3.bridgesSold CurrentBridgesSold, month3.bridgeSales CurrentBridgeSales, month3.totalSales CurrentTotalSales …This will present the user with a lovely readout, something like this:
| TotalSalesMet_ThisMonth | BridgesSoldMet_ThisMonth | BridgeSalesMet_ThisMonth | salesGuyID | SalesGuyName | salesPeriodMonth | salesPeriodYear | CurrentBridgesSold | CurrentBridgeSales | CurrentTotalSales |
| YES | no | no | 1 | Dwight | 3 | 2010 | 1 | 10000 | 300000 |
| no | YES | YES | 16 | Pam | 3 | 2010 | 3 | 260000 | 387000 |
Beautiful. Happy days, all!
-Jen McCown