Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement