CODEWITHSUNDEEP
mysql
Phantom
Phantom

Reputation: 688

MySql: Select active subscritions grouped by month

I have the following MySQL-Database-Table with subscriptions. Each subscription as a startdate and an enddate.

id  | start_date | end_date
  1 | 2017-01-01 | 2017-07-01
  2 | 2017-01-15 | 2017-07-12
  3 | 2017-02-01 | 2017-08-01
  4 | 2017-03-01 | 2017-08-01
  5 | 2017-03-12 | 2017-08-12
  6 | 2017-03-30 | 2017-08-30
  7 | 2017-05-01 | 2017-11-01
  8 | 2017-06-01 | 2017-12-01
  9 | 2017-07-01 | 2018-01-01
 10 | 2017-08-01 | 2018-02-01
 11 | 2018-01-01 | 2018-07-01
 12 | 2018-02-01 | 2018-08-01
 13 | 2018-03-01 | 2018-09-01
... |        ... |        ...

I would like to select all active subscritions within each month. Is this possible with one SQL-Query? I would like to know: How many acitve subscriptions there were in January, February, March, etc. For example the query for just June 2017 would be:

SELECT COUNT(*) FROM table 
WHERE start_date <= '2017-06-30' AND 
      end_date >= '2017-06-01'

I hope my text is understandable.

Upvotes: 1

Views: 306

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521589

This approach uses a calendar table where each month is represented by the first day of that month. Then, we only need to left join this calendar table to your current table using overlapping ranges to find the number of subscriptions for each month.

SELECT
    c.month,
    COUNT(t.start_date) AS num_subscriptions
FROM
(
    SELECT '2017-01-01' AS month UNION ALL
    SELECT '2017-02-01' UNION ALL
    ...
    SELECT '2018-12-01'
) c
LEFT JOIN yourTable t
    ON c.month <= t.end_date AND LAST_DAY(c.month) >= t.start_date
GROUP BY
    c.month;

enter image description here

Demo

Upvotes: 1

kkica
kkica

Reputation: 4104

Use functions LAST_DAY(date) FIRST_DAY(date)

LAST_DAY is a valid function, but FIRST_DAY is not.

You can create a custom function.

DELIMITER ;;
CREATE FUNCTION FIRST_DAY(day DATE)
RETURNS DATE DETERMINISTIC
BEGIN
  RETURN ADDDATE(LAST_DAY(SUBDATE(day, INTERVAL 1 MONTH)), 1);
END;;
DELIMITER ;

Answer copied from Stéphane's answer here

Upvotes: 0

Related Questions