Untitled

mail@pastecode.io avatar
unknown
plain_text
7 months ago
11 kB
7
Indexable
Never
-- 3.1.1.1 --

SELECT
--                CASE 
--                    when sum(ddm.masterPembiayaan_BSI_20230531.sisaPokokPSAK) IS NULL THEN 0
--                    ELSE sum(ddm.masterPembiayaan_BSI_20230531.sisaPokokPSAK)
--                END AS sisaPokokPSAK,
--                CASE 
--                    when sum(ddm.masterPembiayaan_BSI_20230531.CKPN) IS NULL THEN 0
--                    ELSE sum(ddm.masterPembiayaan_BSI_20230531.CKPN)
--                END AS ckpn, 
--                CASE 
--                    when sum(ddm.masterPembiayaan_BSI_20230531.imbalanAkanDiterima) IS NULL THEN 0
--                    ELSE sum(ddm.masterPembiayaan_BSI_20230531.imbalanAkanDiterima)
--                END AS  imbalanAkanDiterima
            	ddm.masterPembiayaan_BSI_20230531.sektorEkonomi, ddm.refSektorEkonomiClassification_20230531.SandiLSMK,
            	ddm.masterPembiayaan_BSI_20230531.noRekening, cfa.Agunankreditpembiayaan_20230531.nomorRekening,
            	cfa.Agunankreditpembiayaan_20230531.noAgunan, cfa.Agunan_20230531.noAgunan,
            	cfa.Agunankreditpembiayaan_20230531.nomorRekening, cfa.ATMR_detail_20230531.NomorRekening,
            	ddm.refSektorEkonomiClassification_20230531.ClusterSekonLSMK, cfa.Agunan_20230531.jenisAgunan,
            	cfa.ATMR_detail_20230531.BobotResiko, ddm.masterPembiayaan_BSI_20230531.tanggalTutup,
                ddm.masterPembiayaan_BSI_20230531.imbalanAkanDiterima, ddm.masterPembiayaan_BSI_20230531.CKPN,
            	ddm.masterPembiayaan_BSI_20230531.sisaPokokPSAK
            FROM
                ddm.masterPembiayaan_BSI_20230531
            JOIN ddm.refSektorEkonomiClassification_20230531
            ON
                ddm.masterPembiayaan_BSI_20230531.sektorEkonomi = ddm.refSektorEkonomiClassification_20230531.SandiLSMK
            JOIN cfa.Agunankreditpembiayaan_20230531 
            ON
                ddm.masterPembiayaan_BSI_20230531.noRekening = cfa.Agunankreditpembiayaan_20230531.nomorRekening
            JOIN cfa.Agunan_20230531 
            ON
                cfa.Agunankreditpembiayaan_20230531.noAgunan = cfa.Agunan_20230531.noAgunan
            JOIN cfa.ATMR_detail_20230531 
            ON
            cfa.Agunankreditpembiayaan_20230531.nomorRekening = cfa.ATMR_detail_20230531.NomorRekening
            WHERE 
                ddm.masterPembiayaan_BSI_20230531.kolektibilitas IN (1, 2)
            AND LOWER(ddm.refSektorEkonomiClassification_20230531.ClusterSekonLSMK) = 'financial institution'
            AND LOWER(cfa.Agunan_20230531.jenisAgunan) IN (LOWER('F040502'),LOWER('F04150201'),LOWER('F0415020'),LOWER('F04150204'),LOWER('F04150205'),LOWER('F04150207')) 
        
            AND cfa.ATMR_detail_20230531.BobotResiko <= 100
        
                AND DATEDIFF(DAY, '2023-05-31', ddm.masterPembiayaan_BSI_20230531.tanggalTutup) >= 365

----------------------------------------------------------------------------------------------------------------------------------------

--3.1.2.1--

SELECT
                ddm.masterPembiayaan_BSI_20230531.sektorEkonomi, ddm.refSektorEkonomiClassification_20230531.SandiLSMK,
            	ddm.masterPembiayaan_BSI_20230531.noRekening, cfa.Agunankreditpembiayaan_20230531.nomorRekening,
            	cfa.Agunankreditpembiayaan_20230531.noAgunan, cfa.Agunan_20230531.noAgunan,
            	cfa.Agunankreditpembiayaan_20230531.nomorRekening, cfa.ATMR_detail_20230531.NomorRekening,
            	ddm.refSektorEkonomiClassification_20230531.ClusterSekonLSMK, cfa.Agunan_20230531.jenisAgunan,
            	cfa.ATMR_detail_20230531.BobotResiko, ddm.masterPembiayaan_BSI_20230531.tanggalTutup,
                 ddm.masterPembiayaan_BSI_20230531.imbalanAkanDiterima, ddm.masterPembiayaan_BSI_20230531.CKPN,
            	ddm.masterPembiayaan_BSI_20230531.sisaPokokPSAK
            FROM
                ddm.masterPembiayaan_BSI_20230531
            JOIN ddm.refSektorEkonomiClassification_20230531
            ON
                ddm.masterPembiayaan_BSI_20230531.sektorEkonomi = ddm.refSektorEkonomiClassification_20230531.SandiLSMK
            JOIN cfa.Agunankreditpembiayaan_20230531 
            ON
                ddm.masterPembiayaan_BSI_20230531.noRekening = cfa.Agunankreditpembiayaan_20230531.nomorRekening
            JOIN cfa.Agunan_20230531 
            ON
                cfa.Agunankreditpembiayaan_20230531.noAgunan = cfa.Agunan_20230531.noAgunan
            JOIN cfa.ATMR_detail_20230531 
            ON
            cfa.Agunankreditpembiayaan_20230531.nomorRekening = cfa.ATMR_detail_20230531.NomorRekening
            WHERE 
                ddm.masterPembiayaan_BSI_20230531.kolektibilitas IN (1, 2)
            AND LOWER(ddm.refSektorEkonomiClassification_20230531.ClusterSekonLSMK) = 'financial institution'
            AND LOWER(cfa.Agunan_20230531.jenisAgunan) NOT IN (LOWER('F040502'),LOWER('F04150201'),LOWER('F0415020'),LOWER('F04150204'),LOWER('F04150205'),LOWER('F04150207')) 
        
            AND cfa.ATMR_detail_20230531.BobotResiko <= 100
        
                AND DATEDIFF(DAY, '2023-05-31', ddm.masterPembiayaan_BSI_20230531.tanggalTutup) >= 365

----------------------------------------------------------------------------------------------------------------------------------------

--3.1.4.1.1--

SELECT 
                    ddm.masterPembiayaan_BSI_20230531.sektorEkonomi, ddm.refSektorEkonomiClassification_20230531.SandiLSMK,
            	ddm.masterPembiayaan_BSI_20230531.noRekening,
            	cfa.ATMR_detail_20230531.NomorRekening,
            	ddm.refSektorEkonomiClassification_20230531.ClusterSekonLSMK, 
            	cfa.ATMR_detail_20230531.BobotResiko, ddm.masterPembiayaan_BSI_20230531.tanggalTutup,
            	ddm.masterPembiayaan_BSI_20230531.kolektibilitas, ddm.refSektorEkonomiClassification_20230531.ClusterSekonLSMK,
            	cfa.ATMR_detail_20230531.Keterangan,
                ddm.masterPembiayaan_BSI_20230531.imbalanAkanDiterima, ddm.masterPembiayaan_BSI_20230531.CKPN,
            	ddm.masterPembiayaan_BSI_20230531.sisaPokokPSAK
                FROM 
                ddm.masterPembiayaan_BSI_20230531
                JOIN ddm.refSektorEkonomiClassification_20230531
                ON ddm.masterPembiayaan_BSI_20230531.sektorEkonomi = ddm.refSektorEkonomiClassification_20230531.SandiLSMK 
                JOIN cfa.ATMR_detail_20230531 
                ON ddm.masterPembiayaan_BSI_20230531.noRekening = cfa.ATMR_detail_20230531.NomorRekening 
                WHERE 
                ddm.masterPembiayaan_BSI_20230531.kolektibilitas IN (1,2)
                AND LOWER(ddm.refSektorEkonomiClassification_20230531.ClusterSekonLSMK) <> 'financial institution'
        
            AND LOWER(cfa.ATMR_detail_20230531.Keterangan) <> LOWER('pembiayaan beragunan tempat tinggal') AND cfa.ATMR_detail_20230531.BobotResiko <= 35
        
                AND DATEDIFF(DAY, '2023-05-31', ddm.masterPembiayaan_BSI_20230531.tanggalTutup) >= 365
                
----------------------------------------------------------------------------------------------------------------------------------------

--3.1.4.2.1--

SELECT 
                 
                   ddm.masterPembiayaan_BSI_20230531.sektorEkonomi, ddm.refSektorEkonomiClassification_20230531.SandiLSMK,
            	ddm.masterPembiayaan_BSI_20230531.noRekening,
            	cfa.ATMR_detail_20230531.NomorRekening,
            	ddm.refSektorEkonomiClassification_20230531.ClusterSekonLSMK, 
            	cfa.ATMR_detail_20230531.BobotResiko, ddm.masterPembiayaan_BSI_20230531.tanggalTutup,
            	ddm.masterPembiayaan_BSI_20230531.kolektibilitas, ddm.refSektorEkonomiClassification_20230531.ClusterSekonLSMK,
            	cfa.ATMR_detail_20230531.Keterangan,
            	ddm.masterPembiayaan_BSI_20230531.imbalanAkanDiterima, ddm.masterPembiayaan_BSI_20230531.CKPN,
            	ddm.masterPembiayaan_BSI_20230531.sisaPokokPSAK
                FROM 
                ddm.masterPembiayaan_BSI_20230531
                JOIN ddm.refSektorEkonomiClassification_20230531
                ON ddm.masterPembiayaan_BSI_20230531.sektorEkonomi = ddm.refSektorEkonomiClassification_20230531.SandiLSMK 
                JOIN cfa.ATMR_detail_20230531 
                ON ddm.masterPembiayaan_BSI_20230531.noRekening = cfa.ATMR_detail_20230531.NomorRekening 
                WHERE 
                ddm.masterPembiayaan_BSI_20230531.kolektibilitas IN (1,2)
                AND LOWER(ddm.refSektorEkonomiClassification_20230531.ClusterSekonLSMK) <> 'financial institution'
        
            AND LOWER(cfa.ATMR_detail_20230531.Keterangan) <> LOWER('pembiayaan beragunan tempat tinggal') AND cfa.ATMR_detail_20230531.BobotResiko > 35
        
                AND DATEDIFF(DAY, '2023-05-31', ddm.masterPembiayaan_BSI_20230531.tanggalTutup) >= 365
                
----------------------------------------------------------------------------------------------------------------------------------------
--3.1.7.1.1--

SELECT ddm.masterPembiayaan_BSI_20230531.sektorEkonomi, ddm.refSektorEkonomiClassification_20230531.SandiLSMK,
            	ddm.masterPembiayaan_BSI_20230531.noRekening,
            	cfa.ATMR_detail_20230531.NomorRekening,
            	ddm.refSektorEkonomiClassification_20230531.ClusterSekonLSMK, 
            	cfa.ATMR_detail_20230531.BobotResiko, ddm.masterPembiayaan_BSI_20230531.tanggalTutup,
            	ddm.masterPembiayaan_BSI_20230531.kolektibilitas, ddm.refSektorEkonomiClassification_20230531.ClusterSekonLSMK,
            	cfa.ATMR_detail_20230531.Keterangan,
            	ddm.masterPembiayaan_BSI_20230531.imbalanAkanDiterima, ddm.masterPembiayaan_BSI_20230531.CKPN,
            	ddm.masterPembiayaan_BSI_20230531.sisaPokokPSAK
                FROM 
                ddm.masterPembiayaan_BSI_20230531
                JOIN ddm.refSektorEkonomiClassification_20230531
                ON ddm.masterPembiayaan_BSI_20230531.sektorEkonomi = ddm.refSektorEkonomiClassification_20230531.SandiLSMK 
                JOIN cfa.ATMR_detail_20230531 
                ON ddm.masterPembiayaan_BSI_20230531.noRekening = cfa.ATMR_detail_20230531.NomorRekening 
                WHERE 
                ddm.masterPembiayaan_BSI_20230531.kolektibilitas IN (1,2)
                AND LOWER(ddm.refSektorEkonomiClassification_20230531.ClusterSekonLSMK) <> 'financial institution'
        
            AND LOWER(cfa.ATMR_detail_20230531.Keterangan) = LOWER('pembiayaan beragunan tempat tinggal') AND cfa.ATMR_detail_20230531.BobotResiko <= 35
        
                AND DATEDIFF(DAY, '2023-05-31', ddm.masterPembiayaan_BSI_20230531.tanggalTutup) >= 365
                
Leave a Comment