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

WHERE tlkpWeekEnding.WeekEnding_ID=75

GROUP BY tlkpWeekEnding.WeekEnding_DT, DiceCat.DiceCat_NM

Order by  DiceCat.DiceCat_NM

Here are the results, we move rows to columns:

View1
Weekending	Job Category	Total Listings	HotJobsTotal	MonsterTotal	CareerBuilderTotal
10/12/2003	Accounting/Auditing/Finance	37226	10021	9694	17511
10/12/2003	Banking/Mortgage	10657	2026	2886	5745
10/12/2003	Biotech/Pharmaceutical	7569	2290	2644	2635
10/12/2003	Engineering	20549	3513	5800	11236
10/12/2003	Healthcare	44080	4415	8028	31637
10/12/2003	Information Technology	34309	10637	8924	14748
10/12/2003	Insurance	8364	1900	2718	3746
10/12/2003	Legal	7962	1976	2498	3488
10/12/2003	Science	4636	0	1074	3562
10/12/2003	Telecommunications	3650	1005	1137	1508

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.

Happy Crosstabbing!