Q: SQL Server 2008 R2 - Creating a table of dates using a while statement. Looking for better options? I'm trying to create a table of future dates. It will be easy to do with a while loop, but I know that isn't the most efficient way to do that in SQL. So I was hoping someone could share some ideas on how to do this in SQL set base manner.
Here is my code:
declare @count int, @dd date
set @count=0;
set @dd ='01/04/2013';
while (@count<24)
  begin
    select @dd=dateadd(week, 2,@dd);
    set @count=@count+1;
    select @dd
  end

Thank you in advance...

A: Probably the best way to do this is with a fixed calendar table; go ahead and create a permanent table with all of the business rules you need for handling date logic.
As a workaround, you can do something like (assuming you have more than 24 columns in your database):
DECLARE @dd DATE
SET @dd = '01/04/2013';

SELECT TOP 24 DATEADD(week, 2*rn, @dd) 
FROM (SELECT rn=(ROW_NUMBER() OVER (ORDER BY name)) -1
        FROM sys.columns) c


A: We do something similar with our data warehouse.  It's a two stage operation.  One is to add new records with the primary key field only and the second is to update the other fields of the new records.  The database engine is redbrick.  redbrick syntax is similar to sql server syntax.  
This is the insert query:
insert into period (date)

select
    case when days_in_year((select max(date) from period))=365 -- current max year not leap year
    and days_in_year(dateadd(day,1,(select max(date) from period)))=365 --new year not leap year
    then dateadd(day,365,date)
    else dateadd(day,366,date)end

from period

where date between
    case when days_in_year(dateadd(day,1,(select max(date) from period)))=366 -- new year is leap year
    or days_in_year((select max(date) from period))=366 -- current max year is leap year
            then dateadd(day,-365, (select max(date) from period)) -- Dec 31 of year before current max year
            else dateadd(day,-364, (select max(date) from period)) end --Jan 1 of current max year

and

    case when days_in_year((select max(date) from period))=366 -- current max year is leap year
            then dateadd(day,-1, (select max(date) from period))-- Dec 30 of current max year
            else (select max(date) from period) end -- Dec 31 of current max year

and current_date > dateadd(month,-9,(select max(date) from period))

Note that days_in_year is a redbrick macro.  In this case it's equivalent to a sql server user defined function.  It's equivalent to this redbrick code
extract(dayofyear from date(concat(datename(year,%1),'-12-31')))

where %1 is the argument passed to the macro.
