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

CodePudding user response：

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