0

My SQL Server 2014 table includes a datetime column called CreatedOn.

Every day I need to idenfiy companies that were created on this date exactly 6 months ago or a multiple of 6 months ago (i.e 6 months after the stored datetime, 12 months after, 18 months after, etc). I can write the rest of the query but cannot figure out how to identify the rows that will match the list of dates (i.e Oct 27th 2020, Apr 27th 2020, Oct 27th 2019, Apr 27th 2019, Oct 27th 2018, Apr 27th 2018, etc).

Here is the query to get the companies that were created exactly 6 months ago. How do I adapt the query to include the additional 6 month intervals?

select * 
from work 
where DATEPART(d, CreatedOn) = DATEPART(d, DATEADD(m, -6, getdate()))
  and DATEPART(m, CreatedOn) = DATEPART(m, DATEADD(m, -6, getdate()))
  and DATEPART(yyyy, CreatedOn) = DATEPART(yyyy, DATEADD(m, -6, getdate()))

Sample data

Name          CreatedOn
-------------------------------------
Company A     2020-10-27 13:49:15.597
Company B     2021-04-27 15:25:09.720
Company C     2021-03-16 15:50:01.443
Company D     2018-04-27 21:58:18.903

Data Table

I would want Company A and Company D in my results set

MckayAA
  • 7
  • 5

4 Answers4

0

I think it would be better if you can incorporate a new table like Communication where you can generate the schedule for your companies when they need to send emails. By comparing dates from that table you can send mail to them. It will be a much cleaner approach and you will be able to verify when the companies actually will get the email. You can add more columns to that table IsEmailSend to filter out data and for reporting purposes.

CompanyId | EmailSchedule | IsEmailSend
1 | 2020-10-27 | 1
1 | 2021-04-27 | 0
...

You have to pre-populate and manage this table data based on Company Create date. You can do this by some stored procedure that will be triggered at some interval.

Samrat Alamgir
  • 355
  • 2
  • 13
0

I would separate the datetime ranges in separated variables as is this db<>fiddle sample:

Query:

-- How can I select the first day of a month in SQL?
-- Source: https://stackoverflow.com/a/1520803/12511801

-- First day of the date (6 months back): 
DECLARE @date_start_range DATE = CAST(DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH, -6, GETDATE())), 0) AS DATE);

-- First day of the current month: 
DECLARE @date_end_range DATE = CAST(DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS DATE);

-- Query results where CreatedOn is between current date and 6 months back: 
SELECT W.* 
FROM work AS W
WHERE CAST(W.CreatedOn AS DATE) BETWEEN @date_start_range AND @date_end_range;
GO

Results:

Name      | CreatedOn              
:-------- | :----------------------
Company A | 2020-10-27 13:49:15.597
Company C | 2021-03-16 15:50:01.443
Marco Aurelio Fernandez Reyes
  • 2,071
  • 2
  • 9
  • 33
0

This is a job for DATEDIFF.

Try something like this.

WITH Dates AS (
SELECT DATEDIFF(month, CreatedOn, GETDATE()) monthsago,
       Name
  FROM sample 
)
SELECT * 
  FROM Dates
 WHERE monthsago % 6 = 0
   AND monthsago > 0

See the fiddle.

You'll still have to keep track of the messages you did send to avoid spamming your customers by mistake.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
-1

I was overthinking it. I don't really care about the year and the day of the month will always be the current day (running just before midnight), I just need to get the current month and the 6 months ago month.

select * from work where DATEPART(d, CreatedOn) = DATEPART(d, getdate()) AND (DATEPART(m, CreatedOn) = DATEPART(m, DATEADD(m, -6, getdate())) or DATEPART(m, CreatedOn) = DATEPART(m, getdate()))

Thanks for everybody's help.

MckayAA
  • 7
  • 5