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