Untitled

mail@pastecode.io avatar
unknown
plain_text
7 months ago
9.9 kB
1
Indexable
Never
-- 3.1.1.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) 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