In some situations, you’ll need to work with a list of numbers or dates that are between some start or end, and you don’t have a complete list handy for joining to. Using the script below, you can create one to use in your query – if you wanted to use this in-line in another SQL Statement (and assuming you’re using SQL 2005+), you could either do a subquery or a WITH CTE clause and then join directly to it.
To do it, I’m using a table that has a large number of rows in it, even in an empty database (sys.columns), and then doing a cross-join to ensure that I’ll have enough rows to satisfy my entire range. This table has about 890 rows in an empty databases (or you can use the “model” database if you don’t have any user databases handy), meaning that the cross join yields about 800,000 rows – enough for almost 2200 years of days, or 100 years of hourly increments (change the “dd” in the “DATEADD” statements below to “hh” or even “mi” to do any increment of time you want).
DECLARE @StartDate DATETIME, @EndDate DATETIME SET @StartDate = '2012-12-01' SET @EndDate = '2015-12-31' ;WITH numberlist(number) AS (SELECT RANK() over(order by c1.object_id, c1.column_id, c2.object_id, c2.column_id) from sys.columns c1 cross join sys.columns c2) SELECT DATEADD(dd, number-1, @StartDate) FROM numberlist WHERE DATEADD(dd, number-1, @StartDate) <= @EndDate
I’m using dates above, but if you wanted to use INT instead, it’s pretty straightforward:
DECLARE @Start INT, @End INT SET @Start = 1500 SET @End = 64000 ;WITH numberlist(number) AS (SELECT RANK() over(order by c1.object_id, c1.column_id, c2.object_id, c2.column_id) from sys.columns c1 cross join sys.columns c2) SELECT @Start + number - 1 FROM numberlist WHERE @Start + number - 1 <= @End