A SQL Tip .. In-Line UNION for previous “n” Months

Here’s an interesting requirement:

“A list of Business Units with the period (YYYYMM) and the monthname (e.g. October) against each one for the prior 3 months”.

A simple problem, but here’s my solution:

SELECT SETCNTRLVALUE AS [BUSINESS_UNIT] , 
CONVERT(VARCHAR(6),PREVIOUS_MONTHS.MYDATE,112) AS [YYYYMM],
DATENAME(MONTH,PREVIOUS_MONTHS.MYDATE) AS [MONTH_NAME]
FROM PS_SET_CNTRL_TBL S,
( SELECT DATEADD(MONTH,-1,GETDATE()) AS [MYDATE]
UNION ALL
SELECT DATEADD(MONTH,-2,GETDATE()) 
UNION ALL
SELECT DATEADD(MONTH,-3,GETDATE()) 
) PREVIOUS_MONTHS
ORDER BY 1,2

Of course, the in-line inner UNION ALL could be created as a view if it was going to be reused, but it suffices for this example. Key points to note:

1. The use of UNION ALL to eliminate the SORT you get with UNION – we know the values are unique but the optimizer doesn’t (it probably should though)!
2. Perform the bare functions on the date value (CONVERT,DATENAME) in the outer loop rather than create multiple columns in the in-line SQL.
3. Yes this is a cartesian product but that is perfectly acceptable when you know your data.
4. This example is for the prior 3 months with reference to GETDATE() – extending to prior “n” months and a different reference date is trivial. But be careful what table you use to get the reference date). Typically you might want the “n” prior months before (say) the MAX() date on some transactional table – if so make sure you have an index to make finding that value as quick as possible.
5. This is SQL server syntax – on Oracle you would have to SELECT from DUAL etc.

Enjoy.