Using a Counter Table to Get a Series of Dates

Posted by: Aaron Goldenthal 4/18/2010 9:28 PM

There are cases where having a table of sequential integers in your database can greatly simplify certain types of queries.  One common example is to get a series of dates. To illustrate how this can be used, first we’ll create a table.  In this case there’s only one column which contains the integers, and we’ll set that as the clustered primary key. 1: CREATE TABLE [dbo].[Counters] 2: ( 3: [CounterID] [int] NOT NULL, 4: CONSTRAINT [PK_Counter] PRIMARY KEY CLUSTERED ( [CounterID] ASC ) 5: ) To be able to do something meaningful with this table, we’ll need to add some data.  To make sure all of the ranges we might use will be covered, we’ll add values from –1000 to 1000. 1: DECLARE @i INT 2: SET @i = -1000 3: WHILE (@i <= 1000) 4: BEGIN 5: INSERT INTO [dbo].[Counters] ( [CounterID] ) VALUES ( @i ) 6: SET @i = @i + 1 7: END For this example, we’ll pull the first date of the month for the last 2 years.  Using the Counters table we can do this by using DATEADD to add months to our starting date and restricting the range from the Counters table to the number of months we require.  For this example, our minimum value is –24 (2 years, or 24 months, prior to the starting date), and our maximum value is 0 (the current date).  Our start date is the first day of a month, so I haven’t included any logic to calculate the first day of the month, but that may be required in some cases. 1: DECLARE @Date DATETIME 2: SET @Date = '4/01/2010' 3:  4: SELECT DATEADD(mm, c.[CounterID], @Date) AS [Date] FROM [dbo].[Counters] c 5: WHERE c.[CounterID] BETWEEN -24 AND 0 If this type of query is going to be used in multiple places in your project, it may be easier to create a table valued function that can pull the date range in question.  This function will take in a start date, the number of months prior to that date, and the number of months after that date (the variables from our previous query). 1: CREATE FUNCTION [dbo].[udf_GetMonthlyDateRange] 2: ( 3: @Date DATETIME, 4: @StartMonths INT, 5: @EndMonths INT 6: ) 7: RETURNS TABLE 8: AS RETURN 9: ( 10: SELECT DATEADD(mm, [CounterID], @Date) AS [Date] 11: FROM [dbo].[Counters] c 12: WHERE c.[CounterID] BETWEEN @StartMonths AND @EndMonths 13: ) Now, using this function we can pull the same data with: 1: SELECT [Date] FROM [dbo].[udf_GetMonthlyDateRange]('4/01/2010', -24, 0)
Categories: SQL
E-mail | Kick it! | DZone it! | Permalink | Comments (1) | Post RSSRSS comment feed