Help with the SQL structure for scheduling notifications

Question

Hi I have the following problem and am trying to solve which is the best solution.

I have a table called [Kpi] and the data will be like this: -

[ContractId]    [KpiId]      [NotificationIntervalInMonths]
 1000           1               3
 1000           2               5

I have a table [Contract] that contains: -

[ContractId]    [StartDate]     [EndDate]
 1000           1/Nov/2009      4/Apr/2011

I am going to show a schedule of notifications about when Kpi ​​should notify the user between the start and end dates of the contract, for example. The following columns / rows will be created in the above structure: -

[ContractId]    [KpiId]      [NotificationDate]
   1000            1            1/Feb/2009
   1000            1            1/May/2010
   1000            1            1/Aug/2010
   1000            1            1/Nov/2010
   1000            1            1/Feb/2011 
   1000            2            1/Apr/2010
   1000            2            1/Sep/2010
   1000            2            1/Feb/2011

First of all, I thought that I was creating a lookup table that would populate every time I inserted a new Kpi, this seems doable and might seem the best.

- , [NotificationIntervalInMonths] , [EndDate] . , / [EndDate], ​​ .

, , SQL, ? : P, , CTE .

, , .

Answer 1

, - /, , , ...

declare @startDate datetime, @endDate datetime
set @startDate = '01/Nov/2009'
set @endDate = '04/Apr/2011'

declare @kpi table(kpiid int, interval int)
insert into @kpi 
select 1, 3 
union select 2, 5
--union select 3, 9 
--union select 4, 12

;with mycte(i, d, interval, p, kpiid) as
(
 select i = 1, d=@startDate, Interval, 0, kpiid from @kpi 
 union all
 select 
  i = i + 1, 
  dateAdd(mm, i, @StartDate), 
  interval,  
  case when (datediff(mm, @StartDate, m.d)) % interval = (interval - 1) then 1 else 0 end,
  m.kpiid
 from 
  mycte m where m.d < @EndDate
)
select * from mycte where p = 1 and d <=@EndDate order by kpiid, d

Answer 2

, 1- . . , , - Skype (, !).:)