 |
SQL Puzzle #42
[reply]
|
06/14/04 07:18 AM EST
posted by JER email web |
|
I was reading SQL Puzzles & Answers and came across a great example of why I love this book.
Our little restaurant business extends lines of credit to popular customers and stores accounts receivable in the "FriendsOfPepperoni" table like so:
custid int
billdate date
amt double
There is no primary key, this is just an "old fashioned journal file, done as a SQL table."
Management wants to know how this line of credit experiment is working out, so our job is to create a report summarizing what each customer owes for 0-30 days, 31-60 days, 61-90 days and 90+.
My immediate thought was to write 4 queries with a derived "age" column and connect them with a UNION operator, like so:
SELECT custid, '0-30 days' AS age, SUM(amt)
FROM FriendsOfPepperoni
WHERE billdate BETWEEN CURRENT_DATE AND (CURRENT_DATE - INTERVAL 30 DAY)
GROUP BY custid
UNION
SELECT custid, '31-60 days' AS age, SUM(amt)
FROM FriendsOfPepperoni
WHERE billdate BETWEEN (CURRENT_DATE - INTERVAL 31 DAY)AND (CURRENT_DATE - INTERVAL 60 DAY)
GROUP BY custid
UNION
SELECT custid, '61-90 days' AS age, SUM(amt)
FROM FriendsOfPepperoni
WHERE billdate BETWEEN (CURRENT_DATE - INTERVAL 61 DAY)AND (CURRENT_DATE - INTERVAL 90 DAY)
GROUP BY custid
UNION
SELECT custid, '90+ days' AS age, SUM(amt)
FROM FriendsOfPepperoni
WHERE billdate < (CURRENT_DATE - INTERVAL 90 DAY)
GROUP BY custid
ORDER BY custid, age
That query was presented as answer #1, but was followed by "This query works, but it takes a while. There must be a better way to do this in SQL-92."
And there is! Solution #2 uses the CASE expression so that instead of 4 passes over our table, we make just one (the syntax will vary slightly, depending on your SQL language):
SELECT custid,
SUM(CASE WHEN billdate BETWEEN CURRENT_DATE AND (CURRENT_DATE - INTERVAL 30 DAY) THEN amt ELSE 0.00) AS age1,
SUM(CASE WHEN billdate BETWEEN (CURRENT_DATE - INTERVAL 31 DAY) AND (CURRENT_DATE - INTERVAL 60 DAY) THEN amt ELSE 0.00) AS age2,
SUM(CASE WHEN billdate BETWEEN (CURRENT_DATE - INTERVAL 61 DAY) AND (CURRENT_DATE - INTERVAL 90 DAY) THEN amt ELSE 0.00) AS age3,
SUM(CASE WHEN billdate < (CURRENT_DATE - INTERVAL 91 DAY) THEN amt ELSE 0.00) AS age4
FROM FriendsOfPepperoni |
|
|
|
| Awesome...I've actually done this. I was doing aggregation counts from one table where the rows could be different types of business. My example looked almost exactly like the above except I checked the value of my column against the business type rather than a date range...nice. |
|
|
|
| I've used CASE to eliminate UNIONs before, but I'm not sure that I've ever nested the CASE logic in an aggregate function so elegantly. And if I had used it, I'd completely forgotten and had regressed to solution #1. |
|
|
|
| This would be perfect if used for a baseball stat tracking application so you can see how someone is performing at various intervals, calculating stats and various intervals all based on a handful of "real" statistics like at bats, hits, errors, singles, doubles, triples, homeruns, and walks. |
|
|
|
| Ooo, hells yeah. Imagine how great sports games would be if they ran these types of queries, so that when Alex Rodriguez comes up to bat it displays "Avg. For Season" vs. "Avg. Over the Last 12 games" if there is a statistically relevant difference. |
|
[reply]
|
06/15/04 05:04 AM EST
posted by nate web |
|
| Dammit Jerry! You've tempted me for years with this SQL puzzle book and I finally must have it. What year was it published and do you know if there is a newer version available? |
|
|
|
| Amazon I always buy from amazon.com Independent resellers. often find NEW books for lots cheaper. I recently purchased SQL Puzzles and Joe's SQL for Smarties. They should arrive any day now. :) |
|
|
|
| If you search for Joe Celko, you can't miss it. Unfortunately, there is no new edition, but most puzzles & answers are SQL-92 compliant, so while you won't be able to use nifty T-SQL tricks, you will learn cool new ways of using the basics (and may not need some of those tricks). |
|