SP Deposito

mail@pastecode.io avatar
unknown
plain_text
2 months ago
13 kB
2
Indexable
Never
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;
Leave a Comment