One of my favorite sessions at the latest PASS conference was that given by Jen McCown (blog | twitter)entitled “T-SQL Tips and Tricks”. It was a light-hearted, informative session on those little things you can do in your T-SQL code that improves efficiency, reduces the load on resources, or just looks really, really cool to us SQL Server geeks. One such topic she covered that looks really, really cool and improves efficiency was Common Table Expressions. I had heard about them, looked into them, and tried to understand them (Books Online, Google, I don’t remember), but never really grasped the concept. Jen was able to knock this one out of the park in a way that changed it from clear as mud to clear as the bright blue sky (insert favorite Guns-N-Roses lyrics here).
Now that I understood what CTEs were, I was wondering if I’d ever get a chance to put them into practice. For some of you who may read this blog this may be elementary, but I’m a newbie at CTEs, and there are probably others out there also who didn’t get a chance to attend Jen’s session, so I thought I’d write a little blurb on how I put my newfound knowledge to work.
We have a report that measures our Sales vs. our goals for the year. It’s broken out by various categories and subcategories so our executive committee can track where we are doing well and where we need to step up the pace a bit. Up until now the report has been fairly simple. Grab all production records for a given time period, summing the production numbers, grouping on the categories and subcategories. An example of this would be “Preneed” for a category, “Preneed Re” (reinsurance) or “Preneed Life” (direct sale) as a subcategory, and “One Pay”, “Three Pay”, or “Five Pay” as a sub-sub category (click on the image for a clearer view).
This year there’s a twist. Not only do they want to see the report as described above, but the executive committee wanted it broken down by other categories not readily available from the given data. Instead of Preneed Re and Preneed Life as the sub categories, they wanted to see Preneed, Preneed sold as Final Expense, and Trust business as the second category. Based on data from other files, I was able to pull in a couple other fields that would help me identify this business. When it was all said and done, I had an indicator that told me if it was Preneed (“P”), Preneed sold as Final Expense (“F”), or Trust business (“T”).
So, now I needed to group by columns that only existed as an indicator. I tried using a case statement, assigning a column value based on the indicator, but found I couldn’t group by the case statement. Here comes CTE to the rescue! Here’s the script I wrote to handle that:
This gives me the following output, which can easily be rendered in a spreadsheet or crystal report (I know, I know, not SSRS, but Accounting hasn’t gotten with the times yet):
It’s as simple as that. I’ve shared this with our BI people in Marketing and they are already scouring their stored procedures to find where they can use this simple, time saving code. Thanks again, Jen!