WITH
ACTIVITY AS
(SELECT
(CASE WHEN closing_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON = 1 THEN 'Aegon'
WHEN closing_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON IS NULL THEN 'Aegon-Finco'
WHEN closing_INVESTOR_ID = 6127857 AND OPENING_INVESTOR_ID = 6127857 THEN 'Access'
WHEN CLOSING_INVESTOR_ID = 6127857 AND OPENING_INVESTOR_ID = 8271826 THEN 'Cork'
WHEN CLOSING_INVESTOR_ID = 6127857 AND OPENING_INVESTOR_ID IS NULL THEN 'Access'
WHEN closing_INVESTOR_ID = 7048910 THEN 'Fort-1'
WHEN closing_INVESTOR_ID =7788504 THEN 'Fort-2'
WHEN closing_INVESTOR_ID = 8271826 AND OPENING_INVESTOR_ID = 8271826 THEN 'Cork'
WHEN closing_INVESTOR_ID = 8271826 AND OPENING_INVESTOR_ID IS NULL THEN 'Cork'
WHEN CLOSING_INVESTOR_ID = 8271826 AND OPENING_INVESTOR_ID = 6127857 THEN 'Access'
WHEN closing_INVESTOR_ID = 8794037 AND OPENING_INVESTOR_ID = 8794037 THEN 'Fort-3'
WHEN closing_INVESTOR_ID = 8794037 AND OPENING_INVESTOR_ID IS NULL THEN 'Access' ELSE 'Review' END) AS Investor,
sum(PPI_TOPUP_REPORTING_MONTH) AS PPI_TOPUP,
sum(PRINCIPAL_REPAYMENTS) AS PRINCIPAL_REPAYMENTS,
sum(IFNULL( DEFAULT_Amount,0)) AS Defaulted,
sum(CASE WHEN DEFAULT_AMOUNT IS NOT NULL THEN (CURRENT_PRINCIPAL_BALANCE_PREV-DEFAULT_AMOUNT) ELSE 0 END) AS DEFAULT_DIFF,
sum(INTEREST_REPAYMENTS)AS INTEREST_REPAYMENTS,
sum(PRINCIPAL_RECOVERIES) AS PRINCIPAL_RECOVERIES,
Sum(INTEREST_RECOVERIES) AS INTEREST_RECOVERIES,
sum(CASE
WHEN DEFAULT_AMOUNT IS NOT NULL AND PRincipal_recoveries IS NULL AND closing_INVESTOR_ID = 764626 THEn DEFAULT_amount*0.44
WHEN DEFAULT_AMOUNT IS NOT NULL AND PRincipal_recoveries IS NULL AND closing_INVESTOR_ID <> 764626 THEn DEFAULT_amount*0.35
WHEN DEFAULT_AMOUNT IS NOT NULL AND PRincipal_recoveries = 0 AND closing_INVESTOR_ID = 764626 THEn DEFAULT_amount*0.44
WHEN DEFAULT_AMOUNT IS NOT NULL AND PRincipal_recoveries = 0 AND closing_INVESTOR_ID <> 764626 THEn DEFAULT_amount*0.35
ELSE 0 END) AS Expected_Recoveries
FROM RAW_RISK.FINANCE_EXTRACT_APR2023_v2 feap
GROUP BY CASE WHEN closing_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON = 1 THEN 'Aegon'
WHEN closing_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON IS NULL THEN 'Aegon-Finco'
WHEN closing_INVESTOR_ID = 6127857 AND OPENING_INVESTOR_ID = 6127857 THEN 'Access'
WHEN CLOSING_INVESTOR_ID = 6127857 AND OPENING_INVESTOR_ID = 8271826 THEN 'Cork'
WHEN CLOSING_INVESTOR_ID = 6127857 AND OPENING_INVESTOR_ID IS NULL THEN 'Access'
WHEN closing_INVESTOR_ID = 7048910 THEN 'Fort-1'
WHEN closing_INVESTOR_ID =7788504 THEN 'Fort-2'
WHEN closing_INVESTOR_ID = 8271826 AND OPENING_INVESTOR_ID = 8271826 THEN 'Cork'
WHEN closing_INVESTOR_ID = 8271826 AND OPENING_INVESTOR_ID IS NULL THEN 'Cork'
WHEN CLOSING_INVESTOR_ID = 8271826 AND OPENING_INVESTOR_ID = 6127857 THEN 'Access'
WHEN closing_INVESTOR_ID = 8794037 AND OPENING_INVESTOR_ID = 8794037 THEN 'Fort-3'
WHEN closing_INVESTOR_ID = 8794037 AND OPENING_INVESTOR_ID IS NULL THEN 'Access' ELSE 'Review' END
),
ACCESS_TRANSFERS AS
(SELECT CONCAT('Access') AS Investor,
SUM(CASE
WHEN OPENING_INVESTOR_ID = 6127857 AND CLOSING_INVESTOR_ID = 8271826 THEN -CURRENT_PRINCIPAL_BALANCE
WHEN OPENING_INVESTOR_ID = 8271826 AND CLOSING_INVESTOR_ID = 6127857 THEN CURRENT_PRINCIPAL_BALANCE
WHEN OPENING_INVESTOR_ID IS NULL AND CLOSING_INVESTOR_ID = 8794037 THEN -CURRENT_PRINCIPAL_BALANCE
WHEN OPENING_INVESTOR_ID = 6127857 AND CLOSING_INVESTOR_ID = 8794037 THEN -CURRENT_PRINCIPAL_BALANCE
ELSE 0 END) AS Transfer_Access
FROM RAW_RISK.FINANCE_EXTRACT_APR2023_V2 feap
),
Cork_Transfers AS
(SELECT CONCAT('Cork') AS Investor,
SUM(CASE
WHEN OPENING_INVESTOR_ID = 6127857 AND CLOSING_INVESTOR_ID = 8271826 THEN CURRENT_PRINCIPAL_BALANCE
ELSE 0 END) AS Transfer_Cork
FROM RAW_RISK.FINANCE_EXTRACT_APR2023_v2 feap
),
Fort3_Transfers AS
(SELECT CONCAT('Fort-3') AS Investor,
SUM(CASE WHEN OPENING_INVESTOR_ID IS NULL AND CLOSING_INVESTOR_ID = 8794037 THEN CURRENT_PRINCIPAL_BALANCE
WHEN OPENING_INVESTOR_ID = 6127857 AND CLOSING_INVESTOR_ID = 8794037 THEN CURRENT_PRINCIPAL_BALANCE
ELSE 0 END) AS Transfer_Fort3
FROM RAW_RISK.FINANCE_EXTRACT_APR2023_v2 feap
),
Closing AS
(SELECT (CASE
WHEN CLOSING_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON = 1 THEN 'Aegon'
WHEN CLOSING_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON IS NULL THEN 'Aegon-Finco'
WHEN CLOSING_INVESTOR_ID = 6127857 THEN 'Access'
WHEN CLOSING_INVESTOR_ID = 7048910 THEN 'Fort-1'
WHEN CLOSING_INVESTOR_ID =7788504 THEN 'Fort-2'
WHEN CLOSING_INVESTOR_ID = 8271826 THEN 'Cork'
WHEN CLOSING_INVESTOR_ID = 8794037 THEN 'Fort-3' ELSE 'Review' END) AS Investor,
SUM(CURRENT_PRINCIPAL_BALANCE) AS Closing_Balance,
sum(CASE WHEN closing_status = 1 AND CURRENT_PRINCIPAL_BALANCE > 0 THEN
(IFNULL(CURRENT_PRINCIPAL_BALANCE_PREV,0)*CURRENT_INTEREST_RATE/360*DAYS_BETWEEN(cutoff_date,CASE WHEN date_disbursement < ADD_MONTHS(cutoff_date,-1) AND date_first_instalment_current > cutoff_date THEN DATE_disbursement ELSE ADD_MONTHS(cutoff_date,-1) END)) ELSE 0 END) AS Interest_Accrued
FROM RAW_RISK.FINANCE_EXTRACT_APR2023_v2 feap
GROUP BY
CASE WHEN CLOSING_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON = 1 THEN 'Aegon'
WHEN CLOSING_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON IS NULL THEN 'Aegon-Finco'
WHEN CLOSING_INVESTOR_ID = 6127857 THEN 'Access'
WHEN CLOSING_INVESTOR_ID = 7048910 THEN 'Fort-1'
WHEN CLOSING_INVESTOR_ID =7788504 THEN 'Fort-2'
WHEN CLOSING_INVESTOR_ID = 8271826 THEN 'Cork'
WHEN CLOSING_INVESTOR_ID = 8794037 THEN 'Fort-3' ELSE 'Review' END)
SELECT
COALESCE(opening.investor,newloans.investor, closing.investor, activity.investor, access_transfers.investor, cork_transfers.investor, fort3_transfers.investor) AS Investor,
round(IFNULL(opening_balance,0),2) AS Opening_Balance,
round(IFNULL(origination,0),2) AS origination, round(IFNULL(ppi_topup,0),2) AS PPI_Added, -round(principal_repayments,2) AS repayments, -round(DEFAULTed,2) AS defaulted,
round(closing_balance,2) AS Closing_Balance,
round((IFNULL(transfer_fort3,0)+IFNULL(transfer_access,0)+IFNULL(transfer_cork,0)),2) AS transferred,
round((IFNULL (opening_balance,0)+IFNULL(origination,0)+IFNULL(ppi_topup,0)-IFNULL(principal_repayments,0)-IFNULL(defaulted,0)-IFNULL(closing_balance,0)+IFNULL(transfer_fort3,0)+IFNULL(transfer_access,0)+IFNULL(transfer_cork,0)),2) AS Check_Closing,
round(-default_Diff,2) AS Default_Variance,
round(opening_balance+IFNULL(origination,0)+IFNULL(ppi_topup,0)-IFNULL(principal_repayments,0)-IFNULL(defaulted,0)-IFNULL(closing_balance,0)+IFNULL(transfer_fort3,0)+IFNULL(transfer_access,0)+IFNULL(transfer_cork,0)-IFNULL(default_diff,0),2) AS Remaining_Variance,
round(IFNULL(interest_repayments,0),2) AS interest_repayments,
round(IFNULL(interest_recoveries,0),2) AS interest_recoveries,
round(IFNULL(principal_recoveries,0),2) AS principal_recoveries,
round(IFNULL(interest_repayments,0)+IFNULL(interest_recoveries,0)+IFNULL(principal_recoveries,0)+IFNULL(principal_repayments,0),2) AS Total_Collections,
round(IFNULL(expected_recoveries,0),2) AS Expected_Recoveries,
CASE WHEN Closing_Balance>0 THEN Round(IFNULL(interest_accrued,0),2) ELSE 0 end AS Interest_Accrued
FROM closing
LEFT JOIN opening ON opening.investor = closing.investor
LEFT JOIN newloans ON newloans.investor = closing.investor
LEFT JOIN activity ON activity.investor = closing.investor
LEFT JOIN Access_Transfers ON access_transfers.investor = closing.investor
LEFT JOIN Cork_Transfers ON Cork_transfers.investor = closing.investor
LEFT JOIN Fort3_transfers ON Fort3_Transfers.investor = closing.investor
Order BY Investor I must convert the sql code to sas code. How to I convert sql code to sas code?
... View more
