Jonathan Livingston Seagull Jonathan Livingston Seagull - 7 months ago 20
SQL Question

First Year Anniversary Next Month

I am trying to make a SQl comand without success:

WITH `params` AS (
SELECT DATEADD(MONTH, 1, CURRENT_TIMESTAMP) AS `nextmonth`
)
SELECT COUNT(*)
FROM `params` CROSS JOIN `Subscribers`
WHERE MONTH(`start_date`) = MONTH(`nextmonth`) AND
YEAR(`start_date`) = YEAR(`nextmonth`) - 1


It aims to get First Year Anniversary Next Month of a customer. But instead I get a syntax error.

Would a good soul help me to find out where I am doing wrong ?

Thanks in advance

Answer

My suggestion... write your predicates to reference the "bare column" start_date from the Subscribers table.

And use expressions to get the lower and upper bounds for the start_date of the subscribers you want to return.

Suppose "next month" is May 2016.

The subscribers that will have a 1 year anniversary date next month are the subscribers who had a start_date in May of 2015.

The example query is returning COUNT(*). I suggest that for developing the query and testing, we might want to return the start_date from each subscriber to verify we're getting the correct result.

I'd write the query in this form:

 SELECT s.*
   FROM `Subscribers` s
  WHERE s.start_date >=  dateexpr1 
    AND s.end_date   <   dateexpr2

For dateexpr1, I'd use something like

DATE_FORMAT(NOW(),'%Y-%m-01') + INTERVAL -11 MONTH

And for datexpr2, I'd use

DATE_FORMAT(NOW(),'%Y-%m-01') + INTERVAL -10 MONTH

We can test those expressions...

SELECT NOW()
     , DATE_FORMAT(NOW(),'%Y-%m-01') + INTERVAL -11 MONTH AS lb
     , DATE_FORMAT(NOW(),'%Y-%m-01') + INTERVAL -10 MONTH AS ub

returns:

NOW()                lb          ub          
-------------------  ----------  ----------
2016-04-20 23:54:32  2015-05-01  2015-06-01   

If this month is April 2016, then next month is May 2016. Won't all of the subscribers that have a "One Year Anniversary Next Month" have a start_date sometime in May 2015?

 SELECT s.*
   FROM `Subscribers` s
  WHERE s.start_date >= DATE_FORMAT(NOW(),'%Y-%m-01') + INTERVAL -11 MONTH
    AND s.start_date <  DATE_FORMAT(NOW(),'%Y-%m-01') + INTERVAL -10 MONTH

That's how I would do it.

In terms of MySQL performance, this pattern (using predicates with bare columns) will enable MySQL to make effective use of a range scan operation on an index which has start_date as the leading column.

If we want to move those messy expressions to an inline view, we could do something like this:

 SELECT s.*
   FROM ( SELECT DATE_FORMAT(NOW(),'%Y-%m-01') + INTERVAL -11 MONTH AS lb
        ) p
   JOIN `Subscribers` s
     ON s.start_date >= p.lb
    AND s.start_date <  p.lb + INTERVAL 1 MONTH