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!