The Case for Case
Ok I have gotten a lot of press because I love the Rozenshtein Method for creating cross tab queries. I have even traveled the world and spoke about it all summer at TechEds around the world.
I have gotten flack, especially from annoying Australians, about how “complex” the Rozenshtein Method is. So today I found myself writing a crosstab and used the Case Method.
It was an ad-hoc query that I have to run a few times over the next few weeks. It never has to run in another database like Access or Oracle. It was needed to be quick and dirty. I decided NOT to use the Boolean aggregates and use a Case statement. The basic structure of a case statement is as follows:
CASE FieldName WHEN ValueYouAreChecking THEN TrueExpression ELSE FalseExpression END
Pretty easy no? Here is a sample:
SELECT tlkpWeekEnding.WeekEnding_DT as Weekending, DiceCat.DiceCat_NM as 'Job Category', Sum(tblData.TotalListings) AS 'Total Listings', SUM(CASE Service_ID WHEN 1 THEN TotalListings ELSE 0 END) AS HotJobsTotal,
SUM(CASE Service_ID WHEN 2 THEN TotalListings ELSE 0 END) AS MonsterTotal,
SUM(CASE Service_ID WHEN 3 THEN TotalListings ELSE 0 END) AS CareerBuilderTotal
FROM DiceCat INNER JOIN ((tlkpWeekEnding INNER JOIN (trelServiceURL INNER JOIN tblData ON trelServiceURL.URL_ID = tblData.URL_ID) ON tlkpWeekEnding.WeekEnding_ID = tblData.WeekEnding_ID) INNER JOIN DiceCatDetail ON trelServiceURL.JobCategory_ID = DiceCatDetail.CorzenJobCatID) ON DiceCat.DiceCat_ID = DiceCatDetail.DiceCat_ID
GROUP BY tlkpWeekEnding.WeekEnding_DT, DiceCat.DiceCat_NM
Order by DiceCat.DiceCat_NM
Here are the results, we move rows to columns:
So if this is so easy why oh why do I insist on using the Rozenshtein Method? Well, I don’t insist on using it all the time. I like it because it is super fast and make sense to me (I was into Math as a kid, sorry). I think that you should know both and use the one that you think is most effective for the job at hand.
Page rendered at Tuesday, January 19, 2021 9:22:10 AM (Eastern Standard Time, UTC-05:00)
The opinions expressed herein are my own personal opinions and do not represent
my employer's view in anyway.