Advertisement
GLASHATAY_007

contract_add_information

Oct 11th, 2023
917
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. WITH PAY_DAY AS
  3. (SELECT MAX(execdocdate) PAY_DATE, RNK
  4. FROM dwh_payments
  5. WHERE RNK IN (SELECT RNK FROM RNK_11_10_23)
  6. GROUP BY rnk),
  7. FIN AS (
  8. SELECT
  9.     LOT_ID,
  10.     SUM(CASE WHEN YEAR = 2020 THEN FINSUM ELSE NULL END) F_2020,
  11.     SUM(CASE WHEN YEAR = 2021 THEN FINSUM ELSE NULL END) F_2021,
  12.     SUM(CASE WHEN YEAR = 2022 THEN FINSUM ELSE NULL END) F_2022,
  13.     SUM(CASE WHEN YEAR = 2023 THEN FINSUM ELSE NULL END) F_2023,
  14.     SUM(CASE WHEN YEAR = 2024 THEN FINSUM ELSE NULL END) F_2024,
  15.     SUM(CASE WHEN YEAR = 2025 THEN FINSUM ELSE NULL END) F_2025
  16. FROM DWH_CONTRACT_FIN_KGNTV
  17. WHERE 1=1 AND TYPE = 1
  18. GROUP BY LOT_ID
  19. )
  20. SELECT
  21.      
  22.     C.LOTID,
  23.     C.CONTRACTRNK,
  24.     TRUNC(c.CONTRACTACTUALEXECDATE) CONTRACTACTUALEXECDATE,
  25.     PAY_DAY.PAY_DATE,
  26.     FIN.F_2020,
  27.     FIN.F_2021,
  28.     FIN.F_2022,
  29.     FIN.F_2023,
  30.     FIN.F_2024,
  31.     FIN.F_2025
  32. FROM dwh_contract_kgntv c
  33. LEFT JOIN PAY_DAY ON PAY_DAY.RNK=C.CONTRACTRNK
  34. LEFT JOIN FIN ON FIN.LOT_ID=C.LOTID
  35. WHERE C.CONTRACTRNK IN (SELECT RNK FROM RNK_11_10_23)
  36. ORDER BY C.CONTRACTRNK
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement