SQL loop while statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL loop while statement

Author

Topic

cindy_l
Starting Member

6 Posts

Posted - 2010-05-27 : 01:09:06

Hi there,

for each record in a table i want to add the value in one column (frequency) to another column (date) until the column reaches a value (date >= 01/06/2010). I guess i should use a While loop with an insert statement but cant seem to get it working. Am i on the right track?

Example below with the required result.

Table original:

ID Cost Date Frequency
1 20 1/03/2010 10
2 30 2/03/2010 20
3 40 3/03/2010 30

Result Table:

ID Cost Date Frequency
1 20 1/03/2010 10
1 20 11/03/2010 10
1 20 21/03/2010 10
1 20 31/03/2010 10
1 20 10/04/2010 10
1 20 20/04/2010 10
1 20 30/04/2010 10
1 20 10/05/2010 10
1 20 20/05/2010 10
1 20 30/05/2010 10
2 30 2/03/2010 20
2 30 22/03/2010 20
2 30 11/04/2010 20
2 30 1/05/2010 20
2 30 21/05/2010 20
3 40 3/03/2010 30
3 40 2/04/2010 30
3 40 2/05/2010 30

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-27 : 03:36:12

declare @t table(ID int, Cost int, Dates datetime, Frequency int)
insert into @t
select 1, 20, '1/03/2010', 10 union all
select 2, 30, '2/03/2010', 20 union all
select 3, 40, '3/03/2010', 30

select t1.ID,t1.Cost,dateadd(day,(number-1)*t1.Frequency ,t1.Dates),t1.Frequency from @t as t1,
master..spt_values as t2
where type='p' and number between 1 and Frequency

Madhivanan

Failing to plan is Planning to fail

cindy_l
Starting Member

6 Posts

Posted - 2010-05-27 : 21:08:52

Close! this repeats each record by the frequency but what i want is to keep repeating until the date column equals a specified value. eg until the date column reaches >= 01/06/2010. The frequency is the increment amount.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-28 : 03:02:29

Add this at the end of where clause

and dateadd(day,(number-1)*t1.Frequency ,t1.Dates)<dateadd(month,datediff(month,0,getdate())+1,0)

Madhivanan

Failing to plan is Planning to fail

cindy_l
Starting Member

6 Posts

Posted - 2010-05-31 : 01:23:56

thanks that works great!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-31 : 03:45:06