Extract total occurance of billing date within subscription period MySQL

Question

Trying to find the number of time the billing date occurs inside the entire subscription period.

For example, if subscription period is 26-01-2015 and 24-09-2016 so the billing date is 27th of each month. Hence the query should return 20 as the number of bill cycles completed.

Answer 1

You need to make use of a MySQL number generator to generate the dates series.

Query

SELECT 
 COUNT(*) AS 'number of days'
FROM ( 

  SELECT 
   '2015-01-26' + INTERVAL generator.number DAY AS date
  FROM ( 

    SELECT 
     (@number  := @number + 1) AS number
    FROM (
      SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    ) AS record_1
    CROSS JOIN (
      SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    ) AS record_2
    CROSS JOIN (
      SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    ) AS record_3
    CROSS JOIN (
      SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    ) AS record_4
    CROSS JOIN ( SELECT @number := 0 ) AS init_user_param 
  ) AS generator
) AS dates 
WHERE 
   dates.date BETWEEN '2015-01-26' AND '2016-09-24'
 AND
   DAY(dates.date) = 27

Result

| number of days |
|----------------|
|             20 |

see demo http://sqlfiddle.com/#!9/c61cdb/9