Padding data base on date range join

I have a dataset with dates set to the first of each month. Eg –

I need to join this on this table :

Now normally I would a right join on this clause –

WHERE DATE BETWEEN START DATE AND END DATE

…and it works fine, for DATE 2021-02-01. That falls between 2021-01-30 and 2021-02-12.

Similarly, 2021-03-01 date falls between 2021-02-27 and 2021-03-12

HOWEVER! I get null values for Pay Periods with Start Date 2021-02-13 and End Date 2021-02-26. I want the date from Table A for the month of Feb to be populated for this row too.

I would want the data from Table A, which for Feb (2020-02-01) is 37 units to have two rows, one joined with Start Date 2021-01-30 & End Date 2021-02-12, and the other joined with Start Date 2021-02-13 & End Date 2021-02-26. (Since both Pay Periods fall in February). The result data set should look like this –

This is the query I am using –

SELECT * 
  FROM A 
 RIGHT 
  JOIN B
    ON A.DATE BETWEEN B.START DATE AND B.END DATE

Answer

I assume that the date range 2021-01-15 to 2021-01-29 is missing from Table B example. If so, this query should be able to return your expected result:

SELECT Units, Date, StartDate, EndDate
  FROM tableA a
  LEFT JOIN tableB b
    ON a.Date <= b.EndDate
    AND MONTH(a.Date) = MONTH(b.EndDate)
    ORDER BY b.StartDate

Instead of RIGHT JOIN, I’ve swithced to LEFT JOIN. Then comparing Date from Table A with End Date from Table B with <= (smaller than or equal to) and matching Date month with End Date month using MONTH(). With the example provided (inclusive of assumed missing date range), this will return exactly as your expected result.

Here’s a fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=02c027506954489377a8e484693bff57