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