| Author |
 Topic  |
|
|
cplusplus
Constraint Violating Yak Guru
483 Posts |
 Posted - 04/11/2007 : 12:05:32
|
I want to run a loop for exact number of times.
if i pass days increment : 30 days and create 3 records. and will give a date as reference.
for example the date is todays date:(041107)
i need to run a loop 3 times,
*****************************************
1st record: will be 04/11/07 + 30 days
2nd records: will be 04/11/07 + 60 days
3rd record: will be 04/11/07 + 90 days
********************************************
please i am very sorry if i am confusing you, the user will have a form, on the form he will choose a record as a reference that record is an existing record and will have a duedate. that duedate will be taken as reference date. and then the user choose increment number of days , in my above example i chose 30 days and for number new records to be created for that i chose as 3 records.
the loop should run only for 3 times and foreach record it should incrment 30 days for the existing records duedate.
Please is it possible in the stored procedure.
**********************************************
insert into tbl_expedite(due_date)
select due_date+30 from tbl_copyfromExpedite
**************************************************
Thank you very much for the information....
|
|
|
PeterNeo
Constraint Violating Yak Guru
355 Posts |
Posted - 04/12/2007 : 00:20:52
|
Declare @dt datetime,
@i int,
@d int
Select @dt = getdate(),
@i = 3,
@d = 30
Select dateadd ( dd, number * @d, @dt), number * @d
From master..spt_values
where type = 'p'
and number <= @i
and number <> 0
In the above ex @i is no. of times u want to loop, @d is no. of days u want to increment
@dt is u r date column.
in master..sptvalues u ll get 1-256 numbers. |
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 04/12/2007 : 02:45:09
|
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
|
|
|
|
cplusplus
Constraint Violating Yak Guru
483 Posts |
Posted - 04/12/2007 : 09:38:42
|
Hello Khtan,
The numbers are not going to be fixed, it will be dynamic. it can be 3 or 4 or may be 10
incrementing can be 1 day or 7 days or 30 days or 180 days or 365 days.
Can i use your example in that case. Thank you very much..
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
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 04/12/2007 : 10:26:53
|
Yes. You still can. If the max number of records that you going to have is 10, just add on the "union all select n = 6" etc up to 10.
quote:
incrementing can be 1 day or 7 days or 30 days or 180 days or 365 days.
change this part to variable
select dateadd(day, @days * n, d.due_date)
KH
|
|
|
| |
Topic |
|
|
|
Question for next loop iterations in SP
