Tag: generate

Generate a Series of Months (or Numbers) in SQL

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:

image

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:

image

and end up with a dataset like this:

image

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:

image

I hope this helps you.