Geek asks and answers

SQL query to search users for the monthly anniversary

I am trying to write a query that will find users who celebrate a monthly anniversary with my company so that we can initiate an email to them (i.e. if today is 3/27, she will find people who have subscribed to 1/27, 2/27 , 3/27, 4/27, etc., regardless of the year).

My method is to find users whose "day" part of their registration date is equal to the "day" part of today's date.

SELECT [User Id],[Sign Up Date] 
  FROM [Monthly Account Update] 
 WHERE DATEPART(DAY,[Sign Up Date]) = DAY(GETDATE())

However, this of course does not work for these scenarios:

  • At the end of February, I would not get the opportunity to email people with 29, 30 and 31 monthly anniversary days, as this does not happen. Ideally, when it was February 28th, I just wanted to put people together on the 29th, 30th and 31st dates.

  • For every other month when the date ends on the 30th, I would not send messages to people with the 31st date. Ideally, here I just would like to dump people with 30 and 31 dates.

Is it possible to perform any of these operations using an SQL query?

+5
source share
4 answers

You will need a more complicated sentence where. Here is the brute force method:

SELECT [User Id],[Sign Up Date]
FROM [Monthly Account Update]
WHERE day([Sign Up Date]) = DAY(GETDATE()) or
      (month(getdate()) in (4, 6, 9, 11) and day(getdate()) = 30 and DAY([Sign Up Date]) = 31) or
      (month(getdate()) in (2) and day(getdate()) = 28 and DAY([Sign Up Date]) in (29, 30, 31))

If its feb, find the 28th.

+1
source

Some tricky answers are here! We can greatly simplify:

create function dbo.fnIsMonthlyAnniversary(
    @startDate date,
    @comparisonDate date
)
returns bit
as
begin
    declare @retVal bit
    set @retVal = 0

    declare @deltaMonth int

    set @deltaMonth = datediff( mm, @startDate, @comparisonDate )

    if dateadd( mm, @deltaMonth, @startDate ) = @comparisonDate
    begin
        set @retVal = 1
    end

    return @retVal
end

-- usage:
select dbo.fnIsMonthlyAnniversary( '2012-12-31', CONVERT( date, getdate() ) )

for your use:

SELECT [User Id],[Sign Up Date] 
  FROM [Monthly Account Update] 
 WHERE 1 = dbo.fnIsMonthlyAnniversary( [Sign Up Date], CONVERT( date, getdate() ) )
+1
source

- :

M [], 1 1000, :

SELECT [User Id],[Sign Up Date]
FROM [Monthly Account Update] AS U
JOIN M
ON CAST(getdate() AS DATE) = DATEADD(month,M.months,U.[Sign Up Date]

, . M.months SELECT , .

0

Subtract the month and include each subsequent day from the range of up to 4 days when the addition of the month does not exceed the current date.

DECLARE @Today DATE = '2/28/2013';
SELECT [Current] = @Today, [MonthAgo] = DATEADD(DAY,t.v,DATEADD(MONTH,-1,@Today))
FROM (VALUES(0),(1),(2),(3)) as t(v)
WHERE DATEADD(MONTH,1,DATEADD(DAY,t.v,DATEADD(MONTH,-1,@Today))) <= @Today;

Result for 02/28/2013:

Current    MonthAgo
---------- ----------
2013-02-28 2013-01-28
2013-02-28 2013-01-29
2013-02-28 2013-01-30
2013-02-28 2013-01-31

Result for 3/27/2013:

Current    MonthAgo
---------- ----------
2013-03-27 2013-02-27

Result for 04/30/2013:

Current    MonthAgo
---------- ----------
2013-04-30 2013-03-30
2013-04-30 2013-03-31

... etc..

Edit:

My answer above can be applied by transferring it to the CTE and then directly attaching it to the original request. Note that calls to built-in functions are always limited to four lines for the entire query, so the performance impact of date functions should be negligible:

DECLARE @Today DATE = GETDATE();

; WITH CTE AS (
    SELECT [Current] = @Today, [MonthAgo] = DATEADD(DAY,t.v,DATEADD(MONTH,-1,@Today))
    FROM (VALUES(0),(1),(2),(3)) as t(v)
    WHERE DATEADD(MONTH,1,DATEADD(DAY,t.v,DATEADD(MONTH,-1,@Today))) <= @Today
)
SELECT [User Id],[Sign Up Date] 
FROM [Monthly Account Update]
JOIN CTE ON CTE.[MonthAgo] = [Sign Up Date];
GO
0
source

More articles:


All Articles