Tuesday, October 14, 2003


Source: Stephen Forte's WebBlog

 

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! [Stephen Forte's WebBlog]


7:11:53 PM    trackback []     Articulate []