This little trick has helped me keep SQL code clean when generating data. There are many times where I need to generate a series of months in a sql query. Very often it is needed in order to join two incomplete sets of data by date, or to cross join a set of data to spread it across a series of months. This is a very simple solution which can be adapted for many other needs.
You can start by using a ‘with’ statement to generate the values you need. In this example I will generate the numbers 1 to 12 to represent 12 months.
;with Months AS (SELECT 1 AS MonthNum UNION ALL SELECT MonthNum + 1 as MonthNum FROM Months WHERE MonthNum <12) SELECT * FROM Months
This is the result set:
Let’s say you have a rates table with one record per year for each Hour Type ID. You want to duplicate this information across months. You start with a transaction like this:
and end up with a dataset like this:
You can do this with dates as well. For example:
DECLARE @StartDate datetime DECLARE @EndDate datetime SET @StartDate=DATEADD(month,-6, CONVERT(CHAR(10),GETDATE(),121)) SET @EndDate = GETDATE() ;with AllDates AS ( SELECT @StartDate AS Dates UNION ALL SELECT DateAdd(month,1,Dates) FROM AllDates WHERE Dates<=@EndDate ) SELECT * FROM AllDates
will result in a series of dates like this:
I hope this helps you.