mysql sum group by month and date using a contract start and end date

Question

I have a table full of monthly contracts. There is a monthly price, a start date, and an end date for each. I am trying to graph each month's total revenue and am wondering if it's possible to do this in one query (vs. a query for each month).

I know how to group by month and year in mysql, but this requires a more complex solution that "understands" whether to include in the sum for a given month/year based on the start and end date of the contract.

Shorthand example

|    contract_id    |    price    |     start_date      |       end_date       |
|        1          |     299     | 1546318800 (1/1/19) | 1554004800 (3/31/19) |
|        2          |     799     | 1551416400 (3/1/19) | 1559275200 (5/31/19) |

With this example, there's an overlap in March. Both contracts are running in March, so the sum returned for that month should be 1098.

I'd like to be able to produce a report that includes every month between two dates, so in this case I'd send 1/1/19 - 12/31/19, the full year of 2019 and would hope to see 0 results as well.

|    month    |    year    |    price_sum    |
|      1      |    2019    |      299        |
|      2      |    2019    |      299        |
|      3      |    2019    |      1098       |
|      4      |    2019    |      799        |
|      5      |    2019    |      799        |
|      6      |    2019    |       0         |
|      7      |    2019    |       0         |
|      8      |    2019    |       0         |
|      9      |    2019    |       0         |
|      10     |    2019    |       0         |
|      11     |    2019    |       0         |
|      12     |    2019    |       0         |

Answer 1

I understand that you will be given a range of dates for which you will need to report. My solution requires you to initialize a temporary table, such as date_table with the first day of each month for which you want to report on:

create temporary table date_table (
    d date,
    primary key(d)
);

set @start_date = '2019-01-01';
set @end_date = '2019-12-01';
set @months = -1;
insert into date_table(d)
select DATE_FORMAT(date_range,'%Y-%c-%d') AS result_date from (
    select (date_add(@start_date, INTERVAL (@months := @months +1 ) month)) as date_range
    from mysql.help_topic a limit 0,1000) a
where a.date_range between @start_date and last_day(@end_date);

Then this should do it:

select month(dt.d) as month, year(dt.d) as year, ifnull(sum(c.price), 0) as price_sum
from date_table dt left join contract c on
    dt.d >= date(from_unixtime(c.start_date)) and dt.d < date(from_unixtime(c.end_date))
group by dt.d
order by dt.d
;

Resulting in:

+-------+------+-----------+
| month | year | price_sum |
+-------+------+-----------+
|     1 | 2019 |       299 |
|     2 | 2019 |       299 |
|     3 | 2019 |      1098 |
|     4 | 2019 |       799 |
|     5 | 2019 |       799 |
|     6 | 2019 |         0 |
|     7 | 2019 |         0 |
|     8 | 2019 |         0 |
|     9 | 2019 |         0 |
|    10 | 2019 |         0 |
|    11 | 2019 |         0 |
|    12 | 2019 |         0 |
+-------+------+-----------+

See demo

I am not sure about the semantics of the column end_date. Right now I am comparing the first a follows: start_date <= first_of_month < end_date. Perhaps the test should be start_date <= first_of_month <= end_date, in which case:

dt.d >= date(from_unixtime(c.start_date)) and dt.d < date(from_unixtime(c.end_date))

becomes:

dt.d between date(from_unixtime(c.start_date)) and date(from_unixtime(c.end_date))

With end_date being the last day of the month, it would not matter either way.

Answer 2

Here is a full working script for your problem, which uses a calendar table approach to represent every month in 2019. Specifically, we represent each month using the first of that month. Then, a given price from your table is applicable to that month if there is overlap with the start and end range.

WITH yourTable AS (
    SELECT 1 AS contract_id, 299 AS price, '2019-01-01' AS start_date, '2019-03-31' AS end_date UNION ALL
    SELECT 2, 799, '2019-03-01', '2019-05-31'
),
dates AS (
    SELECT '2019-01-01' AS dt UNION ALL
    SELECT '2019-02-01' UNION ALL
    SELECT '2019-03-01' UNION ALL
    SELECT '2019-04-01' UNION ALL
    SELECT '2019-05-01' UNION ALL
    SELECT '2019-06-01' UNION ALL
    SELECT '2019-07-01' UNION ALL
    SELECT '2019-08-01' UNION ALL
    SELECT '2019-09-01' UNION ALL
    SELECT '2019-10-01' UNION ALL
    SELECT '2019-11-01' UNION ALL
    SELECT '2019-12-01'
)

SELECT
    d.dt,
    SUM(t.price) AS price_sum
FROM dates d
LEFT JOIN yourTable t
    ON d.dt < t.end_date
  AND DATE_ADD(d.dt, INTERVAL 1 MONTH) > t.start_date
GROUP BY
    d.dt;

Demo

Notes:

If your dates are actually stored as UNIX timestamps, then just call FROM_UNIXTIME(your_date) to convert them to dates, and use the same approach I gave above.

I had to use the overlapping date range formula here, because the criteria for overlap in a given month is that the range of that month intersects the range given by a start and end date. Have a look at this SO question for more information on that.

My code is for MySQL 8+, though in practice you may wish to create a bona fide calendar table (the CTE version of which I called dates above), which contains the range of months/years which you want to cover your data set.