contract_add_information

WITH PAY_DAY AS
(SELECT MAX(execdocdate) PAY_DATE, RNK
FROM dwh_payments
WHERE RNK IN (SELECT RNK FROM RNK_11_10_23)
GROUP BY rnk),
FIN AS (
SELECT
    LOT_ID,
    SUM(CASE WHEN YEAR = 2020 THEN FINSUM ELSE NULL END) F_2020,
    SUM(CASE WHEN YEAR = 2021 THEN FINSUM ELSE NULL END) F_2021,
    SUM(CASE WHEN YEAR = 2022 THEN FINSUM ELSE NULL END) F_2022,
    SUM(CASE WHEN YEAR = 2023 THEN FINSUM ELSE NULL END) F_2023,
    SUM(CASE WHEN YEAR = 2024 THEN FINSUM ELSE NULL END) F_2024,
    SUM(CASE WHEN YEAR = 2025 THEN FINSUM ELSE NULL END) F_2025
FROM DWH_CONTRACT_FIN_KGNTV
WHERE 1=1 AND TYPE = 1
GROUP BY LOT_ID
)
SELECT

    C.LOTID,
    C.CONTRACTRNK,
    TRUNC(c.CONTRACTACTUALEXECDATE) CONTRACTACTUALEXECDATE,
    PAY_DAY.PAY_DATE,
    FIN.F_2020,
    FIN.F_2021,
    FIN.F_2022,
    FIN.F_2023,
    FIN.F_2024,
    FIN.F_2025
FROM dwh_contract_kgntv c
LEFT JOIN PAY_DAY ON PAY_DAY.RNK=C.CONTRACTRNK
LEFT JOIN FIN ON FIN.LOT_ID=C.LOTID
WHERE C.CONTRACTRNK IN (SELECT RNK FROM RNK_11_10_23)
ORDER BY C.CONTRACTRNK