Using a loop in a stored procedure

Question

I am trying to write a procedure that inserts rows into a temp table. the table is based on a table of insurance policies indicating the amount of premium received during the term of the policy. The source data consists of trans_date (date of sale) and the dates policy_start and policy_end. those. if the policy is valid for 12 months, we provide 1/12 of the premium every month.

so something like

while trans_month < policy_end month insert to tblUEPtmp select dateadd(mm, 1, trans_date), earned_premium from tblpolicys set trans_date = dateadd(mm, 1, trans_date) 

(I know this is rubbush code, but I'm currently completely baffled)

My problem is that I need to create an additional 11 rows of data and change the transaction date to add 1 month every time before the date of the transaction change date = policy_end.

I researched the use of CTE, but while loops do not fit in CTE ..

- is it something that could fulfill the function of a table of several elements?

Many thanks.

Answer 1

You can defo do this with CTE, for example, this small snippet will demonstrate how to do recursion using dates:

 declare @start DATETIME = '2012-02-01' declare @end DATETIME = '2013-02-01' ;with cte (date) AS ( SELECT @start UNION ALL SELECT DATEADD(mm,1,cte.date) FROM cte WHERE DATEADD(mm,1,cte.date)<@end ) select * from cte 

This will create a list of dates between @start and @end with spaces per month.

You can

• Use your real tables instead of dummy dates
• Run insert into...select ... from cte to insert the required data.

If you can provide more detailed information about your table schema, I can probably help with a more specific example.

Answer 2

Something like that?

 set @trans_date = ... while @trans_date < @policy_end begin insert to tblUEPtmp select trans_date, earned_premium from tblpolicys where {whatever} set @trans_date = dateadd(mm, 1, @trans_date) end