-1

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

David
  • 3,364
  • 10
  • 41
  • 84
  • welcome to stackoverflow david! i do curious what have you tried? i believe this [QA](https://stackoverflow.com/questions/30300664/mysql-how-to-show-all-days-records-in-particular-month) may give some ideas that you can try. its not an exact answer, but you can adapt the query to produce the dates above. – Bagus Tesa Nov 15 '22 at 06:14
  • Subj: "How to get the ocurrence of ..". Text: "I'd like to know how many occurrences of ..". Decide what do you need - the occurencies itself or their amount only. – Akina Nov 15 '22 at 06:21

1 Answers1

0
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

aatwork
  • 2,130
  • 4
  • 17
  • attributing the original QA which features that fabulous `cross join`s will be appreciated. though, i'm not really fond op just get spoonfed without trying. – Bagus Tesa Nov 15 '22 at 12:02