Add extra rows representing each month based on a date column

Question

I have to create extra rows based on a created date that belongs to another table.

Table A

|id|createddate|type|
|1 |01/02/2020 |X   |
|1 |05/03/2020 |X   |

Table B

|id|invoicedate|amount|
|1 |01/01/2020 | 50   |
|1 |01/02/2020 | 30   |

Desirable outcome

|id|invoicedate|amount|
|1 |01/01/2020 | 50   |
|1 |01/02/2020 | 30   |
|1 |05/03/2020 | 30   |

Would you be able to assist me on how to achieve it? Thanks in advance

Answer 1

On provided data needed result can be achieved using full join and lag window function:

--sample data
WITH tableA(id, createddate) AS (
    VALUES (1, '01/02/2020'),
        (1, '05/03/2020')
),
tableB(id, invoicedate, amount) AS (
    VALUES (1, '01/01/2020', 50),
        (1, '01/02/2020', 30)
)

-- sample query
SELECT id, invoicedate,
    coalesce(amount, lag(amount) over (partition by id order by invoicedate)) amount
FROM(
    SELECT coalesce(a.id, b.id) id,
        coalesce(a.createddate, b.invoicedate) invoicedate,
        amount
    from tableA a
    FULL OUTER JOIN tableB b on a.id = b.id and a.createddate = b.invoicedate
)

Output

id	invoicedate	amount
1	01/01/2020	50
1	01/02/2020	30
1	05/03/2020	30