Produce data -> Store in table?

Hi!

I'm quite new to T-SQL and is about to build a small reporting db using SQL.
Most of the data I can move with normal INSERT INTO ... SELECT, but there are some tables that I want to
produce using T-SQL. For example I want to build a Date table like..

Date
Year
Quarter
Month
WeekDay
...

With some precalculated values for each date.

I've searched the forum but have not found any information on how to produce table contents in a good manner. I would appreciate if someone would have the time to point me in the right direction. I do not want to do this by code in my application.

My first thought is to use some kind of Insert Cursor in a While loop...

Pseudo:

declare cursor ex for 'Insert table ... '

while
begin

(produce data)
insert data

end

close cursor

While browsing the net I've got the feeling that you use cursor less in SQL Server than in other db-engines...

Have a nice day!

You could use WHILE loop without cursor:

Code Snippet

create table dates

(

Year int,

Quarter int,

Month int,

WeekDay int,

SomeData decimal

)

go

declare @.start_date datetime

declare @.end_date datetime

set @.start_date = '2007-01-01'

set @.end_date = getdate()

while @.start_date<@.end_date

begin

set @.start_date = dateadd(day, 1, @.start_date)

insert into dates values(

datepart(year,@.start_date),

datepart(quarter,@.start_date),

datepart(month,@.start_date),

datepart(weekday,@.start_date),

rand() --Just for demo

)

end

go

select * from Dates

|||

Best have a calendar table (you can add all the nessasary columns here), fill the table with required date range, join with your table & use the required calculations,

Code Snippet

create table calendar

(

year int,

quarter int,

month int,

weekday int,

date datetime primary key

)

Go

create proc fill_calendar(@.start_date datetime,@.end_date datetime)

as

begin

set nocount on;

while @.start_date <= @.end_date

begin

insert into calendar

select

datepart(year,@.start_date),

datepart(quarter,@.start_date),

datepart(month,@.start_date),

datepart(weekday,@.start_date),

@.start_date

where

not exists(select 1 from calendar where date=@.start_date);

set @.start_date = dateadd(day, 1, @.start_date)

end

end

go

--Fill your calendar when required

exec fill_calendar '1/1/2000','12/31/2007'

select * from calendar

go

select

year --other columns

,sum(yourtable.calculation)

from

calendar

inner join yourtable on yourtable.datefield = calendar.date

group by

year --other columns