mandania (7/2/2015)

---

This is what i am looking for, to come out of the loop once the datediff days are zero. RowNum is a counter that i am using to concatenate to my subscriptionid and adds a month to my subscriptionrowdate until the datediff is zero but at the moment it continuos to generate the rownum and causes it to overflow.

How can i incorporate this code :

case when RowNum <= datediff(day, salesdate, baseenddate)

then DATEADD(mm, RowNum, salesdate)

else 0 end

into this?

SELECT TOP 1000*

,CAST(viasatsubscriptionid as char(8)) +'_'+LTRIM(STR(RowNum))subscriptionrowlog

, DATEADD(mm, RowNum, salesdate) subscriptionrowdate

FROM (

SELECT viasatsubscriptionid

,firstproductregistrationdate

,salesdate

,baseenddate,

ROW_NUMBER() over(Partition by viasatsubscriptionid order by salesdate)-1 RowNum

FROM stage_viasatsubscription --where RowNum <=datediff(day,salesdate,baseenddate)

)viasatsub