Hi Guys,
My company are looking at sending out customer satisfaction surveys to our customers. There are two different date criteria that should trigger the survey email.
1. StartDate + 3 days (+5 days if the current day is Monday)
2. Then every 6 months while the EndDate is null
Surveys will be sent to customers Mon - Fri only
I am struggling with trigger point 2, how do I select data where the StartDate falls within a 6 month incriment (6 months, 12 months, 18 months etc.)
Here is some sample data and my SQL I have so far, based on the above criteria I am looking to select rows 1, 2 and 5
use tempdb
go
if OBJECT_ID('#projects','u') is not null
drop table #projects
create table #projects (ProjectId int not null, CustomerName varchar(100) not null, StartDate date not null, EndDate date)
insert into #projects values (1, 'Joes Timber Supplies', '2017-10-14', null), -- should be selected as part of the StartDate + 3 days trigger
(2, 'JR MOT Centre', '2017-04-17', null), -- should be selected as part of the 6 monthly trigger
(3, 'Joes Timber Supplies', '2017-10-15', null),
(4, 'Central Demolition', '2017-08-11', '2017-08-12'),
(5, 'Jills Florist', '2016-04-17', null), -- should be selected as part of the 6 monthly trigger
(6, 'The Claim Guys', '2017-10-15', '2017-10-15'),
(7, 'SG Joinery Services', '2017-10-13', null),
(8, 'Surex Fabrication', '2017-02-27', '2017-03-11'),
(9, 'J and M Printing', '2016-02-12', '2016-02-12'),
(10, 'Nuts and Bolts Hardware', '2017-03-12', null)
declare @date date = '2017-10-17'
set datefirst 7
select *
from #projects
where StartDate = iif(datepart(dw,@date)=2,dateadd(day,-5,@date),dateadd(day,-3,@date)) -- if the day is Monday, go back 5 days
Surveys will be sent every day (Mon - Fri)
I am using SSIS to generate a csv file and pass it to file location
Thanks