| Author |
 Topic  |
|
|
cplusplus
Constraint Violating Yak Guru
483 Posts |
 Posted - 04/15/2007 : 19:13:51
|
I want to create 3 sets of same existing record with different due_dates in it.
i pass 3 as thre new rows and 30 as increment days, so want a for next loop 3 times. first time for first set of record duedate will be due_date + 30days, for second record due_date + 60 days, for third record due_date + 90
i want to use the following query, can you please help me.
for i = i +30
insert into tbl_review(reviewby,due_date)
select reviewby,due_date+30 from tbl_mytaskitems where masterid=23
next
Please help thank you very much. |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 04/15/2007 : 19:31:46
|
still on the same issue http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81950 ?
insert into tbl_review(reviewby, due_date)
select reviewby, due_date + (30 * n)
from tbl_mytaskitems
cross join (select 1 as n union all select 2 as n union all select 3 as n) n
where masterid = 23
KH
|
 |
|
|
cplusplus
Constraint Violating Yak Guru
483 Posts |
Posted - 04/15/2007 : 21:06:32
|
Hello KH,
At the crossjoin i am confused, the number can be 3 or 4 or 5 or 6 , it can be any number dynamic.
****************************************************************
cross join (select 1 as n union all select 2 as n union all select 3 as n) n
****************************************************************
Thank you very much for the information...
quote:
Originally posted by khtan
still on the same issue http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81950 ?
insert into tbl_review(reviewby, due_date)
select reviewby, due_date + (30 * n)
from tbl_mytaskitems
cross join (select 1 as n union all select 2 as n union all select 3 as n) n
where masterid = 23
KH
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 04/15/2007 : 21:17:56
|
quote:
the number can be 3 or 4 or 5 or 6 , it can be any number dynamic.
I have asked you before in the previous thread. What is the maximum possible number you are expecting ?
KH
|
Edited by - khtan on 04/15/2007 21:23:47 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 04/15/2007 : 21:22:59
|
From http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81950#299179
quote:
Originally posted by khtan
what is the max number of records you will be expecting ? If it is typically small, you can just cross join to a derived table as follows. If you are looking at a large number of records, then make use of F_TABLE_NUMBER_RANGE here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
insert into tbl_expedite(due_date)
select dateadd(day, 30 * n, d.due_date)
from tbl_copyfromExpedite d
cross join
(
select n = 1 union all select n = 2 union all select n = 3 union all select n = 4 union all select n = 5
) n
where n <= 3
KH
Assuming that the maximum number that you are expecting is 5, you can use the above query. For 3 records, you just need to specify n <= 3
KH
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 04/15/2007 : 21:31:32
|
IF for whatever reason your business rules does not implicate this or you can't decide what is the max or the max number of records is large ?
quote:
If you are looking at a large number of records, then make use of F_TABLE_NUMBER_RANGE here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
declare @n int -- @n is the number of records that you need
insert into tbl_expedite(due_date)
select dateadd(day, 30 * NUMBER, d.due_date)
from tbl_copyfromExpedite d
cross join F_TABLE_NUMBER_RANGE(1, @n)
KH
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 04/15/2007 : 21:33:54
|
OR JUST USE THE WHILE LOOP
declare @i int
select @i = 1
WHILE (@i < 5) -- for 5 records
BEGIN
insert into tbl_review(reviewby, due_date)
select reviewby, due_date + (30 * @i)
from tbl_mytaskitems
where masterid = 23
select @i = @i + 1
END
KH
|
|
|
|
cplusplus
Constraint Violating Yak Guru
483 Posts |
Posted - 04/16/2007 : 00:16:20
|
Really appreciate... Thanks a Lot KH.
quote:
Originally posted by khtan
OR JUST USE THE WHILE LOOP
declare @i int
select @i = 1
WHILE (@i < 5) -- for 5 records
BEGIN
insert into tbl_review(reviewby, due_date)
select reviewby, due_date + (30 * @i)
from tbl_mytaskitems
where masterid = 23
select @i = @i + 1
END
KH
|
|
|
| |
Topic |
|
How to create a copy of rows with different dates
