union.sql

union.sql

CREATE VIEW Combined_Portfolio AS
SELECT 
    portfolio_id,
    amount,
    'Portfolio_two' AS source_table
FROM 
    Portfolio_two
UNION ALL
SELECT 
    t1.portfolio_id,
    t1.amount,
    'Portfolio_one' AS source_table
FROM 
    Portfolio_one t1
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM Portfolio_two t2 
        WHERE t2.portfolio_id = t1.portfolio_id
    );

----------------
WITH Combined_Portfolio AS (
    SELECT 
        portfolio_id,
        amount,
        asofdate,
        'Portfolio_two' AS source_table
    FROM 
        Portfolio_two t2
    WHERE NOT EXISTS (
        SELECT 1 
        FROM Portfolio_one t1 
        WHERE t1.portfolio_id = t2.portfolio_id 
          AND t1.asofdate = t2.asofdate
    )
    UNION ALL
    SELECT 
        t1.portfolio_id,
        t1.amount,
        t1.asofdate,
        'Portfolio_one' AS source_table
    FROM 
        Portfolio_one t1
)
SELECT
    * 
FROM 
    Combined_Portfolio
;