declare @num_of_times int
declare @Count INT
 
DECLARE @newrequestid varchar(50)


DECLARE @Frequency VARCHAR(25), @RequestId INT, @x INT, @Max INT, @RptDesc INT
SET @RequestId = 149906
SET @x = 1


CREATE TABLE #RequestReports (xid int identity(1,1), RptDesc INT)

INSERT INTO #RequestReports (RptDesc)
SELECT RptDesc FROM selected_report where RequestId = @RequestId



SELECT @Frequency = defineschedule from report_request where requestid = @RequestId

IF (@Frequency = 'Quarterly')
BEGIN
	select @num_of_times = ISNULL(datediff(month, receiveddate,expirationdate)/3, 0) from report_request where requestid = @RequestId 
	IF @num_of_times >= 0 SET @num_of_times = @num_of_times + 1
END
ELSE IF (@Frequency = 'Monthly')
BEGIN
	select @num_of_times = ISNULL(datediff(month, receiveddate,expirationdate), 0) from report_request where requestid = @RequestId 
	IF @num_of_times >= 0 SET @num_of_times = @num_of_times + 1
END
ELSE
BEGIN
    SET @num_of_times = 0
END



IF @num_of_times > 0   
BEGIN

SELECT @Max = MAX(XID) FROM #RequestReports

WHILE (@x <= @Max)
BEGIN

SELECT @RptDesc = RptDesc FROM #RequestReports WHERE XID = @x


set @count = 1

WHILE @count <= @num_of_times
BEGIN
     
update table_keys
set key_id = key_id + 1 
where table_name = 'adhoc'

Select @newrequestid = key_id from table_keys where table_name = 'adhoc'

     
INSERT INTO [UnderWriting].[dbo].[renoffcyc]
           ([roc_id]
           ,[roc_ind]
           ,[fundtype]
           ,[prodmth]
           ,[opa_id]
           ,[hlthplnid]
           ,[targetdt]
           ,[actualdt]
           ,[freqcd] )      
SELECT @newrequestid
      ,'O'
       ,RR.fundingtype
       ,case
       when @Frequency = 'Quarterly' Then convert(varchar(30),DATEADD (month , @count*3 ,RR.Receiveddate))
       When @Frequency = 'Monthly' Then convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))
        
       End
           ,SR.Assignto
           ,RR.hlthplnid
           ,''
           ,''
           ,RR.defineschedul   

             
FROM [UnderWriting].[dbo].[REPORT_REQUEST] RR INNER JOIN SELECTED_REPORT SR
ON RR.requestid = SR.requestid
where RR.requestid = @RequestId AND SR.RptDesc = @RptDesc

set @count = @count+1 

END

SET @x = @x + 1
END

END

DROP TABLE #RequestReports