MYSQL: Range matching with anniversaries-CodePudding

I am trying to get records which any of starts_at column anniversaries matches with a range period, but not with ends_at one.

For instance, we have this subscriptions table:

id	starts_at	ends_at
1	2015-01-01	2016-01-01
2	2014-01-01	2017-01-01

And for range 2015-12-01 to 2016-02-01.

I will need the one with ID: 2 because one of its anniversaries (2016-01-01) is in range but ends_at is not.

SQLFiddle

CodePudding user response：

Create a year lookup table like below and insert rows based on your data(say 1980 to 2050):

CREATE TABLE IF NOT EXISTS `year_lookup` (
  `year_value` int,
  PRIMARY KEY (`year_value`)
) DEFAULT CHARSET=utf8;  

INSERT INTO `year_lookup` (`year_value`) VALUES
  ('2010'),
  ('2011'),
  ('2012'),
  ('2013'),
  ('2014'),
  ('2015'),
  ('2016'),
  ('2017');

Then, join the subscription table with yearlookup to find all possible anniversary. Finally, filter the records based on starts_at BETWEEN and end_at NOT BETWEEN as below.

-- Anniversary
SELECT DISTINCT Final.id FROM (
  SELECT s.id AS id, DATE_ADD(MAKEDATE(y.year_value, 1), INTERVAL DAYOFYEAR(starts_at)-1 DAY) AS starts_at, ends_at 
  FROM subscriptions AS s
  INNER JOIN year_lookup AS y
      ON y.year_value BETWEEN YEAR(starts_at) AND YEAR(ends_at)
  ) AS Final
WHERE    
   ((starts_at BETWEEN '2015-12-01' AND '2016-02-01') 
     AND (ends_at NOT BETWEEN '2015-12-01' AND '2016-02-01')) ;

Refer this SQL Fiddle