Help with SQL structure for creating a schedule of notifications

Question

Hi I have the following problem and I am trying to work out what is the best way to solve.

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

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

I have a [Contract] table which contains:-

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

I am after a way to show a schedule of notifications for when the Kpi are due to notify a user between the start and end date of the contract e.g. The structure above would create the following columns/rows:-

[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

I first of all thought that I would create a lookup table that got populated a every time I inserted a new Kpi, this seems feasible and may seem the best approach.

My business logic dictates that the [NotificationIntervalInMonths] can't be changed, however the [EndDate] of a contract can change. This means that I would have to add/delete records in the lookup table based on the new contract [EndDate] and to me this seems a bit messy.

So this leads me to my question, is there a pure SQL approach that can get me a schedule of notifications without creating a look up table? Cursors are not allowed :P but I hoping CTE's would work here.

If I have not given enough information then please ask.

Answer 1

Create a table valued function that accepts your two date ranges and that will return the 1st date in each month in that range. Then join to that function in a query. Sorry, can't give more detail, someone is hassling me on Skype (oh, it's you!). :)

Answer 2

This is the answer, if any body could review it/enhance it then please let me know...

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