Hi There
I am trying to insert some records into a database for budgeting purposes. A
record needs to be inserted for each week.
My query so far looks like this
while [date] between '2005-09-05 00:00:00' and '2006-07-01 00:00:00'
begin
insert into dbo.tbl_budgets ([Date], entity, entity_type, sub_catagory,
amount) values(x, 2, 1, 1, 1375000)
how do i make the x add 7 days to each record?
so output should be
05/09/2005, 2, 1, 1, 1375000
12/09/2005, 2, 1, 1, 1375000
19/09/2005, 2, 1, 1, 1375000
26/09/2005, 2, 1, 1, 1375000
etc etc

Thanks

You can try this ...

insert into dbo.tbl_budgets ([Date], entity, entity_type, sub_catagory,

amount) values(datadd(d,7,x), 2, 1, 1, 1375000)

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. We do not put that silly "tbl-"
on table names, or use reserved words for column names. Thenames you
do have are too vague to be useful.

You should have created either Sequence or Calendar auxiliary table in
your schema. Google them.

INSERT INTO Budgets (budget_date, vague_entity, entity_type,
some_kind-of_sub_catagory, foobar_amt)
SELECT DATE (D, 7*seq, @my_start_date) 2, 1, 1, 1375000
FROM Sequence
WHERE seq BETWEEN 1 AND @my_week_count;

Try code below

declare @date datetime

select @date = '2005-09-05 00:00:00'

while @date between '2005-09-05 00:00:00' and '2006-07-01 00:00:00'
begin
insert into dbo.tbl_budgets
values (@date, entity, entity_type, sub_catagory, amount) values(x, 2, 1,
1, 1375000)

select @date = dateadd(d, 7, @date)
end


Rakesh

[quoted text, click to view]