Date-based tables + columns

[LNBruno]

Sometimes, ya just gotta deal with bad design.

Case in point: I needed to get data which is stored in "monthly" tables. To make it even worse, some of the "monthly" tables have "monthly" columns. Take a look at the result set at the bottom to see examples.

Anyway, here's a block of code I used to set all those date-based variables. Yeah, I ended up having to build dynamic SQL, looping through this to supply the date-based table and/or column names, but like I said; sometimes...

Here's hoping none of you ever have to use this! ;-)

Oh, yeah; this is being run in 2005, but I think it should work with 2000, too.

-- turn off SQL Server's "done in proc" messages:
SET NOCOUNT ON

-- local variables:
DECLARE 
   @number       smallint
   , @StartDate  smalldatetime
   , @EndDate    smalldatetime

-- use TABLE datatype to store numbers used to generate list of dates:
DECLARE @tblNumbers TABLE
   (
   number smallint
   )

-- use TABLE datatype to store all the date-driven variables:
DECLARE @tblRolling12Dates TABLE
   (
   RollingDate     smalldatetime
   , MonthNum      varchar(2)
   , YearNum       varchar(4)
   , LastDay       varchar(2)
   , MonthYear     varchar(5)
   , aCol          varchar(10)
   , zCol          varchar(10)
   , aTableName    varchar(11)
   , zTableName    varchar(11)
   , ProcessedFlag bit
   )

--input parameter; if NULL, defaults to today:
SET @EndDate = GETDATE()

-- fill the numbers table:
SET @number = 0
WHILE @number <= 13
BEGIN
    INSERT @tblNumbers VALUES (@number)
    SET @number = @number + 1
END

-- generate the rolling 12 months dates;
-- working backwards from the end date:
SET @StartDate = DATEADD(mm, -12, @EndDate)

-- get the data:
INSERT @tblRolling12Dates
SELECT DATEADD(mm, number, @StartDate) -1 AS RollingDate
   , CASE
        WHEN LEN(MONTH(DATEADD(mm, number, @StartDate)-1)) = 1
           THEN '0' + CONVERT(varchar,MONTH(DATEADD(mm, number, @StartDate)-1))
           ELSE CONVERT(varchar,MONTH(DATEADD(mm, number, @StartDate)-1))
     END AS MonthNum
   , CONVERT(varchar,YEAR(DATEADD(mm, number, @StartDate)-1))AS YearNum
   , CASE 
        WHEN MONTH(DATEADD(mm, number, @StartDate)-1) IN (1, 3, 5, 7, 8, 10, 12) 
           THEN 31
        WHEN MONTH(DATEADD(mm, number, @StartDate)-1) IN (4, 6, 9, 11) 
           THEN 30
        ELSE CASE -- leap year:
           WHEN (YEAR(DATEADD(mm, number, @StartDate)-1) % 4 = 0 
              AND YEAR(DATEADD(mm, number, @StartDate)-1) % 100 != 0) 
              OR (YEAR(DATEADD(mm, number, @StartDate)-1) % 400 = 0) 
           THEN 29
           ELSE 28
        END
     END AS LastDay 
   , NULL --MonthYear
   , NULL --aCol
   , NULL --zCol
   , NULL --aTableName
   , NULL --zTableName
   , 0    --ProcessedFlag
FROM @tblNumbers
WHERE DATEADD(mm, number, @StartDate) <= @EndDate

-- take what we already have to build the date-based variable names:
UPDATE @tblRolling12Dates
   SET MonthYear = LEFT(DATENAME(m, a.RollingDate),3) + RIGHT(a.YearNum,2)
   , aCol        = YearNum + '-' + a.MonthNum + '-' + '01'
   , zCol        = YearNum + '-' + a.MonthNum + '-' + LastDay
   , aTableName  = 'tblA' + a.YearNum + '_' + MonthNum
   , zTableName  = 'tblZ' + a.YearNum + '_' + MonthNum
FROM @tblRolling12Dates a
JOIN
   (select RollingDate from @tblRolling12Dates) b
ON a.RollingDate = b.RollingDate

--------------------<testing>--------------------
select * from @tblRolling12Dates
--------------------</testing>--------------------

-- turn SQL Server's "done in proc" messages back on:
SET NOCOUNT OFF

/*
RESULTS

RollingDate          MonthNum YearNum LastDay MonthYear  aCol           zCol           aTableName      zTableName  ProcessedFlag
2006-04-19 07:33:00     04     2006     30     Apr06     2006-04-01     2006-04-30     tblA2006_04     tblZ2006_04     0
2006-05-19 07:33:00     05     2006     31     May06     2006-05-01     2006-05-31     tblA2006_05     tblZ2006_05     0
2006-06-19 07:33:00     06     2006     30     Jun06     2006-06-01     2006-06-30     tblA2006_06     tblZ2006_06     0
2006-07-19 07:33:00     07     2006     31     Jul06     2006-07-01     2006-07-31     tblA2006_07     tblZ2006_07     0
2006-08-19 07:33:00     08     2006     31     Aug06     2006-08-01     2006-08-31     tblA2006_08     tblZ2006_08     0
2006-09-19 07:33:00     09     2006     30     Sep06     2006-09-01     2006-09-30     tblA2006_09     tblZ2006_09     0
2006-10-19 07:33:00     10     2006     31     Oct06     2006-10-01     2006-10-31     tblA2006_10     tblZ2006_10     0
2006-11-19 07:33:00     11     2006     30     Nov06     2006-11-01     2006-11-30     tblA2006_11     tblZ2006_11     0
2006-12-19 07:33:00     12     2006     31     Dec06     2006-12-01     2006-12-31     tblA2006_12     tblZ2006_12     0
2007-01-19 07:33:00     01     2007     31     Jan07     2007-01-01     2007-01-31     tblA2007_01     tblZ2007_01     0
2007-02-19 07:33:00     02     2007     28     Feb07     2007-02-01     2007-02-28     tblA2007_02     tblZ2007_02     0
2007-03-19 07:33:00     03     2007     31     Mar07     2007-03-01     2007-03-31     tblA2007_03     tblZ2007_03     0
2007-04-19 07:33:00     04     2007     30     Apr07     2007-04-01     2007-04-30     tblA2007_04     tblZ2007_04     0

*/

< M!ke >