 |
 |
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). |
|
|
|
coach factory outlet has a big heart and a healthy appetite, and a wicked swing with a chainsaw. After a knee injury ended his career as a defensive lineman in college, Coach salvaged his Physical Education degree(barely)and landed a job teaching health at the local high school in his hometown of Savannah.Attention! coach outlet is offering new products at favorable prices for August. Brighter colors, finer looks and newer designs, all bring you a whole new summer.Coach Madison Handbags and Coach Poppy Bags are viewed among probably the most newsworthy among the particular coach factory outlet store so far.coach factory outlet online is a store exclusively at your service. Different kinds of Coach bags are on sale. Get more information from it online store..
coach outlet has always been simple,durable style features to win consumers.The products are more flexible,with easy bleaching,wear characteristics,and simply use a damp cloth.Once visit, you will be unable to restrain loving it. Warmly welcome all of you to the Summer Sales Promotion on coach factory outlet.coach factory outlet online offer New Signature bags which is a the newest cool creation from Coach. The look is positively upscale. Gorgeous lightly textured black leather, beautiful for any season.
In particular, products from coach factory outlet with leather design are fashionable, handmade,leading the wave of American pop.It with simple,durable and unique style to win consumers.coach outlet can provide the coach exactly the same is expected in a retail store. It can help you find bags of various colors, shapes and designs, which prove once again that the coach is actually a selection for the housekeeper.Several days ago, my friends recommended coach factory online to me, which provides women with cheaper Coach goods, better service and faster delivery.There's the recent coach factory outlet online intended for 2011 of our own Louis vuitton outlet store shopping. Most of these bags possess the top quality & lowest cost!
Coach is a leading American designer and maker of luxury lifestyle handbags and accessories.There is no doubt that here coach factory outlet is the exact place you should visit.By the fashion, novel appearance, sophisticated technology, superior quality, coach outlet won domestic and international customers, their products exported to the Middle East, Southeast Asia and other regions.Even if coach factory you invested on a colorful bag you have to choose the right occasion. But sometimes I just want to buy a soft color bag a magenta one.If you are a person concerned about fashion, then you will find more and more women are interested in coach factory outlet online.The production and style of handbag here is well welcomed by consumers.
You know, Coach items are so perfect and fascinating. Now I grow up, and find coach outlet on the Internet offering affordable products with reliable quality.coach factory outlet offers you the modern and very comfortable coach bags. Since many women often do not know things and are daily buses to challenge their handbags handbag, also comprehensive.coach factory outlet online offer New Signature bags which is a the newest cool creation from Coach. The look is positively upscale. Gorgeous lightly textured black leather, beautiful for any season.Appearance fashion, confidence and a perfect coach handbags, you must spend some time and effort to find a division has total choose what you like to for export various kinds of coach outlet store.
The Bags that you can buy in coach outlet online tend to be affordable, feel comfortable knowing that they are simply promoting traditional Coach Handbags.The choices are likely to be basically countless seeing that coach outlet occurs with the help of completely new and also incredible concepts once in a while.Welcome to the coach factory outlet store and Enjoy Shopping Here! We promise all the customers to have the superior qualities and low prices.Now it is the chance for you to be with those special designs. You can find many wonderful cheap coach purses on our coach factory outlets.
No one can deny the shopping at the coach factory outlet is satisfactory. For the low prices and good quality.The reputation of the coach outlet with the good old bag, was actually the original inspiration came from a softball glove, Cheap replica Coach Handbags features soft.coach factory outlet online featured with stylish leather bags and signature materials, and carry this bag, it can designate people seem to be captivatingly feminine and good-looking. Why not to buy one when it is at an affordable price?The Coach Signature Hobo is from the latest release of coach factory outlet store. Its crisp, scribble material, leather handle, perfectly complements the relaxed shape of this stylish pouch.
You know, Coach items are so perfect and fascinating. Now I grow up, and find coach outlet on the Internet offering affordable products with reliable quality.Select luxury high-quality Coach Bags from coach factory outlet.Coach Bags are all-powerful,both have function and a sense of design.Also,they are perfect combinations of ideal appearance and the best utility rates.coach factory outlet online is a fashion brand to ensure its quality. With designer coach shoulder bags, you will always attract people's attention. The bags will emphasize your personal style and taste.
Mainly because products are rattling straight forward, locomotion, to see our own elements connected with coach factory outlet you can actually just make an effort to accomplish without this.On establishing contact with a coach outlet hire agency, a few clarifications would need to be put to rest prior to working out a deal.Coach Madison Handbags and Coach Poppy Bags are viewed among probably the most newsworthy among the particular coach factory outlet store so far.If you are a person concerned about fashion, then you will find more and more women are interested in coach factory outlet online.The production and style of handbag here is well welcomed by consumers. |
|
|
|
Chloé,Chloe,Chloe handbag, cheap Chloe bags, cheap Miu Miu bags;,Chloe handbags,Chloe bag,Chloe bags,Wholesale Chloe handbags, cheap Mulberry bags, cheap Loeve bags,cheap Versace bags, cheap Givenchy bags, cheap Thomas Wylde bags, cheap Balenciaga handbags,Chloe wallet,Chloe wallets,Chloe Purses, cheap Hermes handbags, cheap Louis Vuitton handbagss Balenciaga, cheap Coach handbags, cheap Burberry handbags,Balenciaga bags,Balenciaga bag,Balenciaga handbags,Balenciaga handbag,Wholesale Balenciaga bags,Wholesale Balenciaga handbags,Balenciaga Purses,Balenciaga Wallet,Wholesale Balenciaga bag,Wholesale Balenciaga handbag,Balenciaga Wholesale,Wholesale Balenciaga, cheap Prada handbags, cheap Marc Jacobs handbags, cheap Jimmy Choo handbags, cheap Christian Dior handbags, cheap Fendi handbags, cheap Gucci handbags,Jimmy Choo handbag,Jimmy Choo handbags,Jimmy Choo bags,Jimmy Choo bag, cheap Dolce Gabbana handbags, cheap YSL handbags, cheap Chloe handbags Gucci Full Moon Boston Bag - White Leather 203526CG handbagGucci Full Moon Boston Bag - White Leather 203526CG cheap Miu Miu handbags The Full Moon Boston bag is the essence of chic, with its GG fabric and
Gucci Full Moon Grand Hobo - Brown 203527CB handbagGucci Full Moon Grand Hobo - Brown 203527CB cheap Mulberry handbags Distinguished by a precise shape and eye-catching handle details, this Gucci handbag
Gucci Full Moon Grand Hobo - Cream 203527CG handbagGucci Full Moon Grand Hobo - Cream 203527CG cheap Loeve handbags Distinguished by a precise shape and eye-catching handle details, this Gucci handbag
Gucci Full Moon Large Tote - Black 203528CA2 handbagGucci Full Moon Large Tote - Black 203528CA2 cheap Versace handbags The Full Moon Tote is the perfet bag to carry with you everyday
Gucci Jockey Medium Tote - Brown 203546CB handbagGucci Jockey Medium Tote - Brown 203546CB cheap Givenchy handbags Flirty and adorable, this slouchy Gucci handbag is crafted of monogrammed canvas and
|
|
|
|
Don't be stung online. there's no real need to rush louis vuitton outlet.If you can't walk into a store in or around your local region, online is your only other real-time alternative.Is without a doubt louis vuitton online seriously is present? Is it possible to offer an outlet designed for Louis Vuitton merchandise speaks out?
Have you ever dreamed of being as charming as Madonna? Have you ever thought of becoming an envy of all your friends? If so, come to louis vuitton outlet.There's no doubt that consumers can buy the points subject to their particular cash flow, if you value these particular Louie Vuitton Purses, you could found yourself in your site, and we'll provide bag along with reduced price together with high quality. Prefer to last.
Want to become more charming and faddish? Go to visit louis vuitton outlet webpage and select the most suitable products for yourself.Mainly because mentioned before, you could buy in the louis vuitton online, via the producer's the web outlet. Owning worn it in past times. They can supply a appropriate evaluate for the property's simplicity as well as client service description.This morning, I was looking for a suitable birthday gift for my mother's birthday on the Internet.Suddenly, louis vuitton outlet online burst into my view. Soon I was fascinated by the beauty of its products.
Almost no one can resist the temptation of LV Bags.There established many online louis vuitton outlet recently with the aim of making people's shopping more convenient.Typically the louis vuitton outlet online prominent is every bit clear-cut to uncover your drive all over, the entire a number of strains as well as stuff are actually collated not to mention available in the for instance fashion.If you are hitting the road this summer with shopping as a major destination, most experts suggest that you organize your shopping safari by scouting Web directories to louis vuitton online and to discover what will be on sale when you arrive.
louis vuitton outlet online just like a person who want to show himself in front of other people, because he is confident enough, he can attain the respect of other people.Mainly because mentioned before, you could buy in the louis vuitton online, via the producer's the web outlet. Owning worn it in past times. They can supply a appropriate evaluate for the property's simplicity as well as client service description.
As a classic and fashionable LV manufacturer, louis vuitton outlet online has indeed built up quite a reputation in recent years and enjoys increasing popularity with consumers.The choices are likely to be basically countless seeing that louis vuitton outlet occurs with the help of completely new and also incredible concepts once in a while.LV has been the symbol of fashion and nobility for quite a long time. The louis vuitton online shop has become so famous all around the world recently. |
|
Note: Only registered ShinyDonkey.com users
can post images. Only administrators can delete images.
|
|
|