Select data based on multiple date criteria

• Jim-S

  SSCommitted

  Points: 1823

  More actions

  October 17, 2017 at 7:56 am

  #402601

  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 = dateadd(day,-3,@date) and datepart(dw,@date)<>2) or
     (StartDate between dateadd(day,-5,@date) and dateadd(day,-3,@date) and datepart(dw,@date)=2) -- 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

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  October 17, 2017 at 10:41 am

  #1964061

  maybe it would be best to figure out the list of potential dates first, then compare?
  DECLARE @rundate date = '2017-10-17';
DECLARE @comparedate date = CASE WHEN DatePart(dw,@rundate)=2 THEN DateAdd(day, -5, @rundate) ELSE DateAdd(day, -3, @rundate) END;
CREATE TABLE #datelist (CompareDate date NOT NULL);

  --find applicable days
INSERT INTO #datelist
  (CompareDate)
SELECT DATEADD (month, 0-(N*6), @rundate) AS CompareDate
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) tally(N)

  --get the projects
SELECT p.*
FROM #projects p
WHERE StartDate = @comparedate
  OR (EndDate IS NULL AND StartDate IN (SELECT CompareDate FROM #datelist));

  DROP TABLE #datelist;

• Mr. Brian Gale

  SSC-Insane

  Points: 24800

  More actions

  October 17, 2017 at 10:51 am

  #1964066

  This may not be the most efficient method, but could you implement something like this:
  DECLARE @date DATE = '2017-04-17';

  SELECT
    CASE WHEN DATEPART(MONTH, @date) = DATEPART(MONTH, GETDATE())
             OR DATEPART(MONTH, DATEADD(MONTH, 6, @date)) = DATEPART(
                                                                         MONTH ,
                                                                         GETDATE()
                                                                     ) THEN
             'yes'
         ELSE 'no'
    END AS [6monthinc];

  so your where caluse would have at the end something like:
  AND CASE WHEN DATEPART(MONTH, StartDate) = DATEPART(MONTH, GETDATE())
             OR DATEPART(MONTH, DATEADD(MONTH, 6, StartDate )) = DATEPART(
                                                                         MONTH ,
                                                                         GETDATE()
                                                                     ) THEN
             1
         ELSE 0 END = 1
  The only problem I see with your existing code is that if you subtract 3 days from Tuesday, you get Saturday.  

  This may not be the most efficient way of doing it but I think it solves your problem, no?

  EDIT - the AND section should have used StartDate not @date.

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  October 17, 2017 at 11:13 am

  #1964072

  Jim-S - Tuesday, October 17, 2017 7:56 AM

  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

  Trigger point 1 fails to retrieve some startdates. You should correct this first.

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• Jim-S

  SSCommitted

  Points: 1823

  More actions

  October 18, 2017 at 1:01 am

  #1964147

  ChrisM@home - Tuesday, October 17, 2017 11:13 AM

  Jim-S - Tuesday, October 17, 2017 7:56 AM

  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

  Trigger point 1 fails to retrieve some startdates. You should correct this first.

  Thanks for pointing that out. This was only some test code I knocked up but I have fixed it anyway to avoid confusion.

• Jim-S

  SSCommitted

  Points: 1823

  More actions

  October 18, 2017 at 1:05 am

  #1964148

  Chris Harshman - Tuesday, October 17, 2017 10:41 AM

  maybe it would be best to figure out the list of potential dates first, then compare?
  DECLARE @rundate date = '2017-10-17';
  DECLARE @comparedate date = CASE WHEN DatePart(dw,@rundate)=2 THEN DateAdd(day, -5, @rundate) ELSE DateAdd(day, -3, @rundate) END;
  CREATE TABLE #datelist (CompareDate date NOT NULL);

  --find applicable days
  INSERT INTO #datelist
    (CompareDate)
  SELECT DATEADD (month, 0-(N*6), @rundate) AS CompareDate
  FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) tally(N)

  --get the projects
  SELECT p.*
  FROM #projects p
  WHERE StartDate = @comparedate
    OR (EndDate IS NULL AND StartDate IN (SELECT CompareDate FROM #datelist));

  DROP TABLE #datelist;

  Hi Chris, 
  Thanks for replying. I like the idea of using potential dates. I would need to edit it slightly to accommodate weekends but i'm sure I could get it working.

• Jim-S

  SSCommitted

  Points: 1823

  More actions

  October 18, 2017 at 1:09 am

  #1964149

  bmg002 - Tuesday, October 17, 2017 10:51 AM

  This may not be the most efficient method, but could you implement something like this:
  DECLARE @date DATE = '2017-04-17';

  SELECT
    CASE WHEN DATEPART(MONTH, @date) = DATEPART(MONTH, GETDATE())
             OR DATEPART(MONTH, DATEADD(MONTH, 6, @date)) = DATEPART(
                                                                         MONTH ,
                                                                         GETDATE()
                                                                     ) THEN
             'yes'
         ELSE 'no'
    END AS [6monthinc];

  so your where caluse would have at the end something like:
  AND CASE WHEN DATEPART(MONTH, StartDate) = DATEPART(MONTH, GETDATE())
             OR DATEPART(MONTH, DATEADD(MONTH, 6, StartDate )) = DATEPART(
                                                                         MONTH ,
                                                                         GETDATE()
                                                                     ) THEN
             1
         ELSE 0 END = 1
  The only problem I see with your existing code is that if you subtract 3 days from Tuesday, you get Saturday.  

  This may not be the most efficient way of doing it but I think it solves your problem, no?

  EDIT - the AND section should have used StartDate not @date.

  It's absolutely fine to select rows where the StartDate is Saturday. The problem is with StartDates on Wednesday or Thursday as this job won't be running on Saturday and Sunday to pick them up.
  Thanks for replying, I think I can get something working based on the replies I have received so far.