The table below is the result of the modifed script. This works fine for one a row with one or more subid.

subID prodgdate salesdate prodendDate RowNum Subrowlog Subrowdate

2000 2006-02-09 2006-02-09 2006-02-04 0 2000_0 2006-02-27

3000 2007-04-07 2007-04-07 2007-04-09 1 2000_1 2006-03-27

4000 2008-06-02 2008-06-02 2008-06-06 2 2000_2 2006-04-27

but i had made a mistake, i forgot to mention that i want to iterate for number of days

Datediff(days,prodgdate,prodenddate) so for subid i have 5 days of difference and the result should look like this in the end

subID prodgdate salesdate prodendDate RowNum Subrowlog Subrowdate

2000 2006-02-09 2006-02-09 2006-02-04 0 2000_0 2006-02-04

2000 2006-02-09 2006-02-09 2006-02-04 1 2000_1 2006-03-04

2000 2006-02-09 2006-02-09 2006-02-04 2 2000_2 2006-04-04

2000 2006-02-09 2006-02-09 2006-02-04 3 2000_3 2006-05-04

2000 2006-02-04 2006-02-09 2006-02-04 5 2000_4 2006-06-04

3000 2007-04-07 2007-04-07 2007-04-09 0 2000_0 2006-04-07

3000 2007-02-27 2007-04-07 2007-04-09 1 2000_1 2006-05-07

This is the script:

select *, CAST(subID as CHAR(4)) + '_' + CAST(RowNum as char(2)) subscriptionrowlog

-- ,DATEADD(mm, RowNum, prodgdate) subscriptionrowdate

,CASE WHEN RowNum<= DATEDIFF(DAY, salesdate, prodendDate) THEN

DATEADD(mm, RowNum, salesdate) --DATEADD(mm, RowNum, CONVERT(VARCHAR(10),salesdate,112))--DATE OVERFLOW???

ELSE '1900-01-01'

END AS subscriptionrowdate

from (

select subID, prodgdate, salesdate, prodendDate,

ROW_NUMBER() over(Partition by subID order by prodgdate)-1 RowNum

from @t

) a