get all the dates that lie between two dates column by adding the frequency(third column) as a month | mysql

0
votes

I have an invoice table and I want to generate the date range from two-column based on the third column(as frequency). Or you can say that I want to check the current date between date ranges but each date range is different for each row.

invoice table

As you can see in the picture, I have the start date, end date, and frequency. The frequency is in the months. I have two rows and I want to get all the dates that lie between two dates column by adding the frequency(third column) as a month but should be less than or equal to the end_date column.

So I want the date range like below

2020-11-23
2020-12-23
2021-01-23
2021-02-23
2021-03-23
2021-04-23
2021-05-23

because of start_date:- 2020-11-23, end_date:- 2021-06-21, and frequency = 1 in first row

and another date range like below

2020-11-19
2021-01-19
2021-03-19

because of start_date:- 2020-11-19, end_date:- 2021-03-20, and frequency = 2 in second row

So at last, I need to check today's date in the above ranges like

SELECT 
id
FROM `invoices` 
where CURRENT_DATE in(
'2020-11-23', '2020-12-23', '2021-01-23', '2021-02-23', '2021-03-23', '2021-04-23', '2021-05-23', '2020-11-19', '2021-01-19', '2021-03-19')
1
mysqldate

1 Answers

0
votes
WITH RECURSIVE
cte AS ( SELECT id, start_date, end_date, frequency
         FROM test
         UNION ALL
         SELECT id, start_date + INTERVAL frequency MONTH, end_date, frequency
         FROM cte
         WHERE start_date <= end_date )
SELECT id, start_date `date`
FROM cte
WHERE start_date <= end_date
ORDER BY 1,2

fiddle