I have a table in MySQL with records that contain a start_date and an end_date. I'd like to know how many occurrences of the 1st day and the 15th day of the month exist within the period between those two dates.

ie., if start_date is 2021-07-28 and end_date is 2021-10-21 the result should be 6, because all of these dates are included in the period:

2021-08-01
2021-08-15
2021-09-01
2021-09-15
2021-10-01
2021-10-15

Any idea how I could achieve this? Thanks

CodePudding user response：

select count(day_of_month) from (select day(single_date) as day_of_month from
(select (select end_date from test_table where id=1) - INTERVAL (a.a   (10 * b.a)   (100 * c.a)) DAY as single_date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
 ) all_days, test_table
 where all_days.single_date >= test_table.start_date and all_days.single_date <= test_table.end_date and test_table.id=1) all_day_of_month where (day_of_month = 1 or day_of_month = 15);

SQL fiddle link which returns correct output 6 for the given input http://www.sqlfiddle.com/#!9/8054d5/19/0