Creating multiple rows for one row

[ABerkowicz]

Hi. Can someone give me some ideas on how to accomplish the following? I have a table that has the following fields and sample data in it.

dateApplied, endEarning, balRemain, moRemain
2/1/04, 8/1/04, 334209.86, 6

What I need to do is produce a table from this data that looks like this:

2/1/04, 8/1/04, 55701.66, 6
2/1/04, 7/1/04, 55701.64, 6
2/1/04, 6/1/04, 55701.64, 6
2/1/04, 5/1/04, 55701.64, 6
2/1/04, 4/1/04, 55701.64, 6
2/1/04, 3/1/04, 55701.64, 6

The moRemain is the amount of records/rows that I will need to create. I move backwards and create 6 rows from endEarning. The BalRemain field needs to be divided by the moRemain column and then any rounding differences need to be handled in the latest month = endEarning as shown above.

The major thing that I need help in the syntax is how to loop through and create multiple rows from one row based on the logic that I mention. Can someone get me started on this?

Thanks,
Angela

 

[sunila7]

Try this
I am creating a temp table to hold the max no of "MoRemian" so that it can be used to link to the main table to generate data

create table #AllNos(i int)
Declare @MaxNo int
Select @MaxNo = Max(moRemain) from TblName

While @MaxNo > 0
Begin

insert into #Allnos values (@MaxNo)
Set @MaxNo = @MaxNo -1
End

select * from #AllNos

select dateApplied,endEarning,balRemain/moRemain,moRemain from TBLName t
Inner Join #AllNos A on T.moRemain >=A.i


Sunil