1

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.

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
Phantom
  • 638
  • 1
  • 7
  • 18
  • This will be tricky. You will need to use a Master calendar table, as well as a subscription may show up across multiple months. – Madhur Bhaiya Nov 21 '18 at 10:29
  • Please provide a **relevant and minimal sample data** showcasing your requirements (including edge cases), and **corresponding** expected output. – Madhur Bhaiya Nov 21 '18 at 10:30

2 Answers2

1

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

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

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

kkica
  • 4,034
  • 1
  • 20
  • 40