SP Deposito
unknown
plain_text
a year ago
13 kB
6
Indexable
USE [bsi] GO /****** Object: StoredProcedure [mrms].[IRRBB_DepositoRedemptionRateCalc] Script Date: 26/02/2024 14:32:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Use GO to delimit batches -- Use GO to delimit batches -- Add GO here to delimit the batches ALTER PROCEDURE [mrms].[IRRBB_DepositoRedemptionRateCalc] @date VARCHAR(10), @confidenceLevel FLOAT AS /*dihapus jika ingin create SP, hanya untuk testing hasil keluarannya*/ --=============================================================================== --DECLARE @date VARCHAR(10) = '20230630'; --DECLARE @confidenceLevel FLOAT = 95; --============================================================================== BEGIN DECLARE @sql varchar (max) =''; DECLARE @reportDate VARCHAR(10) = SUBSTRING(@date, 1, 4) + '-' + SUBSTRING(@date, 5, 2) + '-' + SUBSTRING(@date, 7, 2); DECLARE @lastMonthDate varchar(max) = FORMAT([mrms].[EOMLastDayWork](DATEADD(DAY, -1, CAST(substring(@date, 1, 6) + '01' AS DATE))), 'yyyyMMdd'); DECLARE @lastMonthDateFormat varchar(max) = FORMAT([mrms].[EOMLastDayWork](DATEADD(DAY, -1, CAST(substring(@date, 1, 6) + '01' AS DATE))), 'yyyy-MM-dd'); DECLARE @oldYear varchar(max) = FORMAT([mrms].[EOMLastDayWork](DATEADD(YEAR, -10, CAST(substring(@date, 1, 6) + '01' AS DATE))), 'yyyy-MM-dd'); DECLARE @RandomString NVARCHAR(100); DECLARE @CharacterPool NVARCHAR(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'; DECLARE @StringLength INT = 10; SET @RandomString = ''; WHILE @StringLength > 0 BEGIN SET @RandomString = @RandomString + SUBSTRING(@CharacterPool, (CAST(RAND() * LEN(@CharacterPool) AS INT) + 1), 1); SET @StringLength = @StringLength - 1; END set @sql = ' DECLARE @DEP_1_IDR FLOAT;DECLARE @smm_DEP_1_IDR FLOAT;DECLARE @percentile_DEP_1_IDR FLOAT; DECLARE @DEP_3_IDR FLOAT;DECLARE @smm_DEP_3_IDR FLOAT;DECLARE @percentile_DEP_3_IDR FLOAT; DECLARE @DEP_6_IDR FLOAT;DECLARE @smm_DEP_6_IDR FLOAT;DECLARE @percentile_DEP_6_IDR FLOAT; DECLARE @DEP_12_IDR FLOAT;DECLARE @smm_DEP_12_IDR FLOAT;DECLARE @percentile_DEP_12_IDR FLOAT; DECLARE @DEP_1_V FLOAT;DECLARE @smm_DEP_1_V FLOAT;DECLARE @percentile_DEP_1_V FLOAT; DECLARE @DEP_3_V FLOAT;DECLARE @smm_DEP_3_V FLOAT;DECLARE @percentile_DEP_3_V FLOAT; DECLARE @DEP_6_V FLOAT;DECLARE @smm_DEP_6_V FLOAT;DECLARE @percentile_DEP_6_V FLOAT; DECLARE @DEP_12_V FLOAT;DECLARE @smm_DEP_12_V FLOAT;DECLARE @percentile_DEP_12_V FLOAT; DECLARE @DEP_1_IDR_ FLOAT;DECLARE @smm_DEP_1_IDR_ FLOAT;DECLARE @percentile_DEP_1_IDR_ FLOAT; DECLARE @DEP_3_IDR_ FLOAT;DECLARE @smm_DEP_3_IDR_ FLOAT;DECLARE @percentile_DEP_3_IDR_ FLOAT; DECLARE @DEP_6_IDR_ FLOAT;DECLARE @smm_DEP_6_IDR_ FLOAT;DECLARE @percentile_DEP_6_IDR_ FLOAT; DECLARE @DEP_12_IDR_ FLOAT;DECLARE @smm_DEP_12_IDR_ FLOAT;DECLARE @percentile_DEP_12_IDR_ FLOAT; DECLARE @DEP_1_V_ FLOAT;DECLARE @smm_DEP_1_V_ FLOAT;DECLARE @percentile_DEP_1_V_ FLOAT; DECLARE @DEP_3_V_ FLOAT;DECLARE @smm_DEP_3_V_ FLOAT;DECLARE @percentile_DEP_3_V_ FLOAT; DECLARE @DEP_6_V_ FLOAT;DECLARE @smm_DEP_6_V_ FLOAT;DECLARE @percentile_DEP_6_V_ FLOAT; DECLARE @DEP_12_V_ FLOAT;DECLARE @smm_DEP_12_V_ FLOAT;DECLARE @percentile_DEP_12_V_ FLOAT; DECLARE @percentileValue FLOAT SET @percentileValue = '+ CAST(@confidenceLevel AS VARCHAR(10)) + '/100.0; SELECT @DEP_1_IDR = SUM(DEP_1_IDR), @DEP_3_IDR = SUM(DEP_3_IDR), @DEP_6_IDR = SUM(DEP_6_IDR), @DEP_12_IDR = SUM(DEP_12_IDR), @DEP_1_V = SUM(DEP_1_V), @DEP_3_V = SUM(DEP_3_V), @DEP_6_V = SUM(DEP_6_V), @DEP_12_V = SUM(DEP_12_V) FROM ( SELECT mdc.jangkaWaktu, mdc.jenisValuta , CASE WHEN mdc.jangkaWaktu = ''01M'' AND mdc.jenisValuta = ''IDR'' THEN mdb.saldoLSMK ELSE 0 END AS DEP_1_IDR , CASE WHEN mdc.jangkaWaktu = ''03M'' AND mdc.jenisValuta = ''IDR'' THEN mdb.saldoLSMK ELSE 0 END AS DEP_3_IDR , CASE WHEN mdc.jangkaWaktu = ''06M'' AND mdc.jenisValuta = ''IDR'' THEN mdb.saldoLSMK ELSE 0 END AS DEP_6_IDR , CASE WHEN mdc.jangkaWaktu = ''12M'' AND mdc.jenisValuta = ''IDR'' THEN mdb.saldoLSMK ELSE 0 END AS DEP_12_IDR , CASE WHEN mdc.jangkaWaktu = ''01M'' AND mdc.jenisValuta != ''IDR'' THEN mdb.saldoLSMK ELSE 0 END AS DEP_1_V , CASE WHEN mdc.jangkaWaktu = ''03M'' AND mdc.jenisValuta != ''IDR'' THEN mdb.saldoLSMK ELSE 0 END AS DEP_3_V , CASE WHEN mdc.jangkaWaktu = ''06M'' AND mdc.jenisValuta != ''IDR'' THEN mdb.saldoLSMK ELSE 0 END AS DEP_6_V , CASE WHEN mdc.jangkaWaktu = ''12M'' AND mdc.jenisValuta != ''IDR'' THEN mdb.saldoLSMK ELSE 0 END AS DEP_12_V FROM ddm.masterDPK_BSI_'+@lastMonthDate+' mdb LEFT JOIN ddm.masterDPK_close_'+@date+' mdc ON mdb.noRekening = mdc.noRekening WHERE mdc.jenisProduk = ''Deposito'' AND format(mdc.closeDate, ''yyyy-MM'') = SUBSTRING('''+@reportDate+''', 1, 7) AND mdc.closeDate < mdc.JkWaktuJatuhTempo AND mdc.jangkaWaktu IN (''01M'', ''03M'', ''06M'', ''12M'') ) AS dta SELECT @DEP_1_IDR_ = SUM(DEP_1_IDR), @DEP_3_IDR_ = SUM(DEP_3_IDR), @DEP_6_IDR_ = SUM(DEP_6_IDR), @DEP_12_IDR_ = SUM(DEP_12_IDR), @DEP_1_V_ = SUM(DEP_1_V), @DEP_3_V_ = SUM(DEP_3_V), @DEP_6_V_ = SUM(DEP_6_V), @DEP_12_V_ = SUM(DEP_12_V) FROM( SELECT mdb.jangkaWaktu, mdb.jenisValuta , CASE WHEN mdb.jangkaWaktu = ''01M'' AND mdb.jenisValuta = ''IDR'' THEN mdb.saldoLSMK ELSE NULL END AS DEP_1_IDR , CASE WHEN mdb.jangkaWaktu = ''03M'' AND mdb.jenisValuta = ''IDR'' THEN mdb.saldoLSMK ELSE NULL END AS DEP_3_IDR , CASE WHEN mdb.jangkaWaktu = ''06M'' AND mdb.jenisValuta = ''IDR'' THEN mdb.saldoLSMK ELSE NULL END AS DEP_6_IDR , CASE WHEN mdb.jangkaWaktu = ''12M'' AND mdb.jenisValuta = ''IDR'' THEN mdb.saldoLSMK ELSE NULL END AS DEP_12_IDR , CASE WHEN mdb.jangkaWaktu = ''01M'' AND mdb.jenisValuta != ''IDR'' THEN mdb.saldoLSMK ELSE NULL END AS DEP_1_V , CASE WHEN mdb.jangkaWaktu = ''03M'' AND mdb.jenisValuta != ''IDR'' THEN mdb.saldoLSMK ELSE NULL END AS DEP_3_V , CASE WHEN mdb.jangkaWaktu = ''06M'' AND mdb.jenisValuta != ''IDR'' THEN mdb.saldoLSMK ELSE NULL END AS DEP_6_V , CASE WHEN mdb.jangkaWaktu = ''12M'' AND mdb.jenisValuta != ''IDR'' THEN mdb.saldoLSMK ELSE NULL END AS DEP_12_V FROM ddm.masterDPK_BSI_'+@lastMonthDate+' mdb WHERE mdb.jenisProduk = ''Deposito'' AND mdb.jangkaWaktu IN (''01M'', ''03M'', ''06M'', ''12M'') ) AS dta SET @smm_DEP_1_IDR = CASE WHEN ISNULL(@DEP_1_IDR_,0) = 0 THEN 0 ELSE ISNULL(@DEP_1_IDR,0) / ISNULL(@DEP_1_IDR_,0) * 100 END SET @smm_DEP_3_IDR = CASE WHEN ISNULL(@DEP_3_IDR_,0) = 0 THEN 0 ELSE ISNULL(@DEP_3_IDR,0) / ISNULL(@DEP_3_IDR_,0) * 100 END SET @smm_DEP_6_IDR = CASE WHEN ISNULL(@DEP_6_IDR_,0) = 0 THEN 0 ELSE ISNULL(@DEP_6_IDR,0) / ISNULL(@DEP_6_IDR_,0) * 100 END SET @smm_DEP_12_IDR = CASE WHEN ISNULL(@DEP_12_IDR_,0) = 0 THEN 0 ELSE ISNULL(@DEP_12_IDR,0) / ISNULL(@DEP_12_IDR_,0) * 100 END SET @smm_DEP_1_V = CASE WHEN ISNULL(@DEP_1_V_,0) = 0 THEN 0 ELSE ISNULL(@DEP_1_V,0) / ISNULL(@DEP_1_V_,0) * 100 END SET @smm_DEP_3_V = CASE WHEN ISNULL(@DEP_3_V_,0) = 0 THEN 0 ELSE ISNULL(@DEP_3_V,0) / ISNULL(@DEP_3_V_,0) * 100 END SET @smm_DEP_6_V = CASE WHEN ISNULL(@DEP_6_V_,0) = 0 THEN 0 ELSE ISNULL(@DEP_6_V,0) / ISNULL(@DEP_6_V_,0) * 100 END SET @smm_DEP_12_V = CASE WHEN ISNULL(@DEP_12_V_,0) = 0 THEN 0 ELSE ISNULL(@DEP_12_V,0) / ISNULL(@DEP_12_V_,0) * 100 END DROP TABLE IF EXISTS mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' CREATE TABLE mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' ( periode datetime2 NOT NULL, componentCode VARCHAR(16), balance NUMERIC(32,2) DEFAULT 0, ew NUMERIC(32,2) DEFAULT 0, smm FLOAT(32) DEFAULT 0, percentile NUMERIC(23,16) DEFAULT 0 ) INSERT INTO mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' (periode,balance,ew,smm,componentCode,percentile) SELECT periode,balance,ew,smm,componentCode,percentile FROM mrms.irrbb_depositoRedemptionRate WHERE isDeleted = 0 AND periode >= '''+@oldYear+''' AND periode <= '''+@lastMonthDateFormat+''' INSERT INTO mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' (periode,balance,ew,smm,componentCode) VALUES ('''+@reportDate+''',ISNULL(@DEP_1_IDR,0),ISNULL(@DEP_1_IDR_,0),ISNULL(@smm_DEP_1_IDR,0),''DEP_1_IDR''), ('''+@reportDate+''',ISNULL(@DEP_3_IDR,0),ISNULL(@DEP_3_IDR_,0),ISNULL(@smm_DEP_3_IDR,0),''DEP_3_IDR''), ('''+@reportDate+''',ISNULL(@DEP_6_IDR,0),ISNULL(@DEP_6_IDR_,0),ISNULL(@smm_DEP_6_IDR,0),''DEP_6_IDR''), ('''+@reportDate+''',ISNULL(@DEP_12_IDR,0),ISNULL(@DEP_12_IDR_,0),ISNULL(@smm_DEP_12_IDR,0),''DEP_12_IDR''), ('''+@reportDate+''',ISNULL(@DEP_1_V,0),ISNULL(@DEP_1_V_,0),ISNULL(@smm_DEP_1_V,0),''DEP_1_V''), ('''+@reportDate+''',ISNULL(@DEP_3_V,0),ISNULL(@DEP_3_V_,0),ISNULL(@smm_DEP_3_V,0),''DEP_3_V''), ('''+@reportDate+''',ISNULL(@DEP_6_V,0),ISNULL(@DEP_6_V_,0),ISNULL(@smm_DEP_6_V,0),''DEP_6_V''), ('''+@reportDate+''',ISNULL(@DEP_12_V,0),ISNULL(@DEP_12_V_,0),ISNULL(@smm_DEP_12_V,0),''DEP_12_V'') SELECT top 1 @percentile_DEP_1_IDR = PERCENTILE_CONT(@percentileValue) WITHIN GROUP (ORDER BY smm) OVER () FROM mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' where componentCode = ''DEP_1_IDR'' GROUP BY componentCode , smm, periode SELECT top 1 @percentile_DEP_3_IDR = PERCENTILE_CONT(@percentileValue) WITHIN GROUP (ORDER BY smm) OVER () FROM mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' where componentCode = ''DEP_3_IDR'' GROUP BY componentCode , smm, periode SELECT top 1 @percentile_DEP_6_IDR = PERCENTILE_CONT(@percentileValue) WITHIN GROUP (ORDER BY smm) OVER () FROM mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' where componentCode = ''DEP_6_IDR'' GROUP BY componentCode , smm, periode SELECT top 1 @percentile_DEP_12_IDR = PERCENTILE_CONT(@percentileValue) WITHIN GROUP (ORDER BY smm) OVER () FROM mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' where componentCode = ''DEP_12_IDR'' GROUP BY componentCode , smm, periode SELECT top 1 @percentile_DEP_1_V = PERCENTILE_CONT(@percentileValue) WITHIN GROUP (ORDER BY smm) OVER () FROM mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' where componentCode = ''DEP_1_V'' GROUP BY componentCode , smm, periode SELECT top 1 @percentile_DEP_3_V = PERCENTILE_CONT(@percentileValue) WITHIN GROUP (ORDER BY smm) OVER () FROM mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' where componentCode = ''DEP_3_V'' GROUP BY componentCode , smm, periode SELECT top 1 @percentile_DEP_6_V = PERCENTILE_CONT(@percentileValue) WITHIN GROUP (ORDER BY smm) OVER () FROM mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' where componentCode = ''DEP_6_V'' GROUP BY componentCode , smm, periode SELECT top 1 @percentile_DEP_12_V = PERCENTILE_CONT(@percentileValue) WITHIN GROUP (ORDER BY smm) OVER () FROM mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' where componentCode = ''DEP_12_V'' GROUP BY componentCode , smm, periode UPDATE mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' SET percentile = @percentile_DEP_1_IDR WHERE componentCode = ''DEP_1_IDR'' AND periode = '''+@reportDate+''' UPDATE mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' SET percentile = @percentile_DEP_3_IDR WHERE componentCode = ''DEP_3_IDR'' AND periode = '''+@reportDate+''' UPDATE mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' SET percentile = @percentile_DEP_6_IDR WHERE componentCode = ''DEP_6_IDR'' AND periode = '''+@reportDate+''' UPDATE mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' SET percentile = @percentile_DEP_12_IDR WHERE componentCode = ''DEP_12_IDR'' AND periode = '''+@reportDate+''' UPDATE mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' SET percentile = @percentile_DEP_1_V WHERE componentCode = ''DEP_1_V'' AND periode = '''+@reportDate+''' UPDATE mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' SET percentile = @percentile_DEP_3_V WHERE componentCode = ''DEP_3_V'' AND periode = '''+@reportDate+''' UPDATE mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' SET percentile = @percentile_DEP_6_V WHERE componentCode = ''DEP_6_V'' AND periode = '''+@reportDate+''' UPDATE mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' SET percentile = @percentile_DEP_12_V WHERE componentCode = ''DEP_12_V'' AND periode = '''+@reportDate+''' select * from mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' WHERE periode = '''+@reportDate+''' DROP TABLE IF EXISTS mrms.irrbb_temp_depositoRedemptionRate'+@RandomString+' '; --print(@sql) exec (@sql) END;
Editor is loading...
Leave a Comment