Given two dates, count the number of dates in the range that is within month X

Jame_T

I have a MySQL table with start and end dates. E.g rows like this:

start | end
2022-03-01 | 2022-04-10
2021-12-01 | 2022-03-11
2022-01-01 | 2022-04-05
...

I want to count how many of the days in those ranges (all rows) were in April 2022 for instance. The first record has 10 days in April, the third record has 5 days in April, so the result should be 15.

Craig Robbins

So what ideas have you considered so far?

CHill60

As you have not shared your attempt at solving this for yourself, nor responded to Craig, I am only going to give you hints to a possible solution. For my test data I used this

declare @demo table (startdate date, enddate date)
insert into @demo (startdate, enddate) values
('2022-03-01', '2022-04-10'),
('2021-12-01', '2022-03-11'),
('2022-01-01', '2022-04-05');

I also hard-coded the start and end dates for the month I am interested in

declare @1stDay date = '2022-04-01';
declare @LastDay date = '2022-04-30';

I then wrote some sql that would convert the dates I had on the table to only those that fell into the month I am looking at. E.g. Something like this

select startdate, enddate

,case	when startdate < @1stDay then @1stDay 
		when startdate > @lastDay then @LastDay
		else startdate end as AmendedStartDate
,case	when enddate < @1stDay then @1stDay
		when enddate > @LastDay then @LastDay
		else enddate end as AmendedEndDate

from @demo;

I then used those Amended dates in a datediff calculation to get the number of days in each range that fell in April 2022. This approach is flawed - in that I get 9, 0 and 4 as the results instead of 10, 0, 5. I will leave that as an exercise for you to sort out. I also coded this in MSSQL so you may need to make minor syntactical changes