With datediff() I can count the days between two dates, but how can I count the days between the later date or the end of the month and the start date?

```
CREATE TABLE table1 (id int, start_date datetime, end_date datetime, jan int);

INSERT INTO table1 (id, start_date, end_date) VALUES 
(1, '2016-12-12', '2017-01-17'), 
(2, '2017-01-10', '2017-01-10'), 
(3, '2017-01-10', '2017-02-10'), 
(4, '2017-01-03', '2017-02-03'),
(5, '2016-12-03', '2017-02-03');

```

If I run:

```
select id, month(start_date) as month, datediff(end_date, start_date) as diff
from table1;

```

it returns

```
id  month   diff
1   12      36
2   1       0
3   1       31
4   1       31
5   12      62

```

but I would like it to return:

```
id  month   diff
1   12      19
5   12      28
1   1       17
2   1       0
3   1       21
4   1       28
5   1       31
3   2       10
4   2       3
5   2       3

```

I'm trying to get the amount of days in a month a event occurs by month.

I've created a separated query to update a new column with the values, but ideally it shouldn't have a new column, since I would need several new columns for each year-month combination and one for each year-month combination:

```
update table1 set jan= case 
when start_date >= "2017-01-01" and end_date <= last_day("2017-01-01") then datediff(end_date, start_date)+1
when start_date >= "2017-01-01" and start_date <= last_day("2017-01-01") and end_date > last_day("2017-01-01") then datediff(last_day("2017-01-01"), start_date)+1
when start_date < "2017-01-01" and end_date between "2017-01-01" and last_day("2017-01-01") then datediff(end_date, "2017-01-01")+1
when start_date < "2017-01-01" and end_date > last_day("2017-01-01") then day(last_day("2017-01-01"))
else null
end;

```