Get date based on interval days in sql I have a table containing range table like this.

DayFrom Dayto Frequent
60      46    3     = interval 15 days (15/3 = Every 5 days send email for 3 times)
45      37    3     = interval 9 days (9/3 = Every 3 days send email for 3 time)
36      31    6     = interval 6 days (email will sent everyday)


I will sent email based on that table. The case is, there will be an Event/Promotion that start on 1 June 2016. I want to get the date based on that range becoming 1 row. When 2 April = GETDATE() the email will send, the email will send again on 7 April and so on until the range on Range table is finished.

a)  46-60:
1st: 2, April 
2nd: 7, April
3rd: 12, April

b)  37-45:
1st: 17, April 
2nd: 20, April
3rd: 23, April

c)  31-36:
1st: 26, April 
2nd: 27, April
3rd: 28, April
4th: 29, April
5Th: 30, April
6th: 1, Mei


I'm just a newbie to select like in this case.. Usually i'm just query for normal case :(
I've tried the following query. But the frequent is not work.

select h.DayFrom ,h.DayTo ,h.Frequent ,
       ( h.DayFrom - h.DayTo + 1 ) AS AlarmDay ,
       (( h.DayFrom - h.DayTo + 1 ) / h.Frequent ) AS ReminderTimes ,
       ( F.PromotionFrom - ( DATEDIFF(d, GETDATE(), F.PromotionFrom) ) ) AS ReminderDate  from PF F INNER JOIN PendingApprovalDetail H ON F.SBU = H.SBU AND H.DocumentType = 'PF'
AND DATEDIFF(d,GETDATE(),F.PromotionFrom) BETWEEN H.DayTo and H.DayFrom
I base my idea on generating the records for each interval.For this I use master..spt_values.( you can use anything else, that generate a list of numbers 0,1,2...).

So , for interval 60-46, generate 3(frequency) records.Those records have the starting point = the event date - 60 days. (This is the date when you start to send emails)

DECLARE @dtCurrentDT AS DATETIME ;
SET @dtCurrentDT = '20160402';
--SET @dtCurrentDT = '20160403';

WITH EventsCTE AS
( SELECT '20160601' AS EventPromotion)
,RemindersCTE AS
(SELECT 60  aS DayFrom,46 AS Dayto, 3 AS Frequent UNION ALL
 SELECT 45,37,3 UNION ALL
 SELECT 36,31,6)

SELECT 
    R.DayFrom
    ,R.Dayto
    ,R.Frequent 
    ,DATEADD(DAY,
            ((R.DayFrom-R.DayTo + 1)/ R.Frequent) * CA.number , 
            DATEADD(DAY,-R.DayFrom,E.EventPromotion)) AS StartDateEvent_ToSendMail
    --,CA.number 
    --,DATEADD(DAY,((R.DayFrom-R.DayTo + 1)/ R.Frequent) * CA.number , @dtCurrentDT) AS RemainderDay
    --,DATEADD(DAY,-R.DayFrom,E.EventPromotion) AS Event_StartDate
FROM
    RemindersCTE AS R
    CROSS APPLY
    (SELECT TOP(R.Frequent)
        V.number
     FROM
        master..spt_values AS V
     WHERE
        V.type ='P'
     ORDER BY
        V.number ASC
     )CA
     CROSS JOIN EventsCTE AS E
WHERE
    DATEADD(DAY,
            ((R.DayFrom-R.DayTo + 1)/ R.Frequent) * CA.number , 
            DATEADD(DAY,-R.DayFrom,E.EventPromotion))
    &gt;= @dtCurrentDT;


My output is :

DayFrom     Dayto       Frequent    StartDateEvent_ToSendMail
60          46          3           2016-04-02 00:00:00.000
60          46          3           2016-04-07 00:00:00.000
60          46          3           2016-04-12 00:00:00.000
45          37          3           2016-04-17 00:00:00.000
45          37          3           2016-04-20 00:00:00.000
45          37          3           2016-04-23 00:00:00.000
36          31          6           2016-04-26 00:00:00.000
36          31          6           2016-04-27 00:00:00.000
36          31          6           2016-04-28 00:00:00.000
36          31          6           2016-04-29 00:00:00.000
36          31          6           2016-04-30 00:00:00.000
36          31          6           2016-05-01 00:00:00.000