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
Best Answer
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
, generate3
(frequency) records.Those records have the starting point = the event date - 60 days. (This is the date when you start to send emails)My output is :