1

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
2

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))
    >= @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
1

That's a perfect case for recursion, see fiddle:

;WITH PendingApprovalDetail AS
 (
   SELECT 60 AS DayFrom, 46 AS DayTo, 3 AS Frequent 
   UNION ALL
   SELECT 45,37,3 
   UNION ALL
   SELECT 36,31,6
 )
,cte AS
 (
   SELECT 
      -- how many times to send a mail
      h.Frequent as Counter  
      -- days between mails
     ,( h.DayFrom - h.DayTo + 1 ) / h.Frequent AS DaysBetween
      -- first date based on June 1st/GETDATE()
     ,DATEADD(d, -h.DayFrom, CAST('20160601' AS DATE)) AS ReminderDate
  FROM PendingApprovalDetail H
  UNION ALL
  SELECT 
     Counter -1 
    ,DaysBetween
     --next date
    ,DATEADD(d, DaysBetween, ReminderDate)
  FROM cte
  WHERE Counter > 1
) 
SELECT * FROM cte
ORDER BY ReminderDate;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.