CASE expressions are a huge timesaving SQL feature that I have just recently fallen in love with. The specific example was trying to find how the activity of a class of users has declined over a period of twelve months. Without CASE this would have required writing and executing 12 different queries and then joining them into a table either by joining all those queries together or by pasting them into (in this case) excel. That’s hard enough but what about 24hrs in a day for day parting analysis across 100000 keywords (far too big for excel <2007 that one ;) )
CASE selects each of those different queries I described into a column using a clause something like:
SELECT sum( CASE month WHEN 2006M10 THEN revenue ELSE 0 END) as oct06, sum( CASE month WHEN 2006M11 THEN revenue ELSE 0 END) as nov06, sum( CASE month WHEN 2006M12 THEN revenue ELSE 0 END) as dec06 FROM tbl_userrevenue WHERE userid IN (‘alex’, ‘beth’)
Which in my mind is a pretty cool tool for analyzing anything where you want to look at discrete time intervals or users classes or almost anything else individually.
Leave a Reply