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
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
