SP Giro Tabungan
unknown
plain_text
2 years ago
7.5 kB
6
Indexable
USE [bsi]
GO
/****** Object: StoredProcedure [mrms].[IRRBB_Tabungan_Giro] Script Date: 20/12/2023 15:41:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [mrms].[IRRBB_Tabungan_Giro]
@reportDate VARCHAR(10),
@code VARCHAR(100),
@filterQuery VARCHAR(max),
@randomString VARCHAR(max),
@queryTempTabunganGiro VARCHAR(max)
AS
/*dihapus jika ingin create SP, hanya untuk testing hasil keluarannya*/
--===============================================================================
--DECLARE @reportDate VARCHAR(10) = '20230630';
--DECLARE @code VARCHAR(100) = 'TAB_R_IDR';
--DECLARE @filterQuery VARCHAR(max) = '
--[ddm].[masterDPK_BSI_20230630].jenisProduk = ''Tabungan''
--AND [ddm].[masterDPK_BSI_20230630].individu = ''R''
--AND [ddm].[masterDPK_BSI_20230630].JenisValuta = ''IDR''
--';
--DECLARE @queryTempTabunganGiro VARCHAR(max) = '';
--DECLARE @randomString VARCHAR(max) = 'random';
--==============================================================================
BEGIN
declare @sql varchar (max) ='';
declare @mainTable varchar(max) = '#TempRepricingGap_'+@randomString;
declare @masterTable varchar(max) = '#TempQueryFilter_'+@randomString;
declare @placementType varchar (max) ='';
declare @currency varchar (max) ='';
declare @tenor varchar (max) ='';
set @sql = @queryTempTabunganGiro + '
DROP TABLE IF EXISTS '+@mainTable+';
CREATE TABLE '+@mainTable+' (
Nominal DECIMAL(32, 2),
saldoRataRataValue DECIMAL(32, 2),
PortofolioRate FLOAT,
Overnight DECIMAL(32, 2),
Month1 DECIMAL(32, 2),
Month2 DECIMAL(32, 2),
Month3 DECIMAL(32, 2),
Month4 DECIMAL(32, 2),
Month5 DECIMAL(32, 2),
Month6 DECIMAL(32, 2),
Month7 DECIMAL(32, 2),
Month8 DECIMAL(32, 2),
Month9 DECIMAL(32, 2),
Month10 DECIMAL(32, 2),
Month11 DECIMAL(32, 2),
Month12 DECIMAL(32, 2),
Month18 DECIMAL(32, 2),
Year2 DECIMAL(32, 2),
Year3 DECIMAL(32, 2),
Year4 DECIMAL(32, 2),
Year5 DECIMAL(32, 2),
Year6 DECIMAL(32, 2),
Year7 DECIMAL(32, 2),
Year8 DECIMAL(32, 2),
Year9 DECIMAL(32, 2),
Year10 DECIMAL(32, 2),
Year15 DECIMAL(32, 2),
Year20 DECIMAL(32, 2),
Year20Plus DECIMAL(32, 2)
);
INSERT INTO '+@mainTable+'
(Nominal, saldoRataRataValue, PortofolioRate, Overnight, Month1, Month2, Month3, Month4, Month5, Month6, Month7,
Month8, Month9, Month10, Month11, Month12, Month18, Year2, Year3, Year4, Year5,
Year6, Year7, Year8, Year9, Year10, Year15, Year20, Year20Plus)
VALUES
(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0);
';
set @sql = @sql + '
DROP TABLE IF EXISTS '+@masterTable+';
SELECT jkWaktuJatuhTempo as MaturityDate, saldoRataRata, imbalan AS Rate, saldoLSMK
INTO '+@masterTable+'
FROM [ddm].[masterDPK_BSI_'+@reportDate+']
WHERE '+@filterQuery+' ;
--NOMINAL--
UPDATE '+@mainTable+'
SET Nominal =
ISNULL((
SELECT SUM(ISNULL('+@masterTable+'.saldoLSMK, 0))
FROM '+@masterTable+'
),0),
saldoRataRataValue = ISNULL((
SELECT SUM(ISNULL('+@masterTable+'.saldoRataRata, 0))
FROM '+@masterTable+'
),0);
--PORTOFOLIO RATE in percent--
UPDATE '+@mainTable+'
SET PortofolioRate =
CASE WHEN ISNULL(Nominal, 0) = 0 THEN 0
ELSE
ISNULL((
SELECT
SUM(ISNULL(A.saldoRataRata, 0) * ISNULL(A.Rate,0)) / ISNULL(saldoRataRataValue, 0)
FROM '+@masterTable+' A
),0)
END ;
--TIME BUCKET--
UPDATE '+@mainTable+'
SET
Overnight =
ISNULL((
select Nominal * @Overnight from '+@mainTable+'
),0)
, Month1 =
ISNULL((
select Nominal * @1M from '+@mainTable+'
),0)
, Month2 =
ISNULL((
select Nominal * @2M from '+@mainTable+'
),0)
, Month3 =
ISNULL((
select Nominal * @3M from '+@mainTable+'
),0)
, Month4 =
ISNULL((
select Nominal * @4M from '+@mainTable+'
),0)
, Month5 =
ISNULL((
select Nominal * @5M from '+@mainTable+'
),0)
, Month6 =
ISNULL((
select Nominal * @6M from '+@mainTable+'
),0)
, Month7 =
ISNULL((
select Nominal * @7M from '+@mainTable+'
),0)
, Month8 =
ISNULL((
select Nominal * @8M from '+@mainTable+'
),0)
, Month9 =
ISNULL((
select Nominal * @9M from '+@mainTable+'
),0)
, Month10 =
ISNULL((
select Nominal * @10M from '+@mainTable+'
),0)
, Month11 =
ISNULL((
select Nominal * @11M from '+@mainTable+'
),0)
, Month12 =
ISNULL((
select Nominal * @12M from '+@mainTable+'
),0)
--, Month18 =
-- ISNULL((
-- SELECT SUM(ISNULL(A.saldoLSMK, 0))
-- FROM xTblTimeBucket_'+@randomString+' A
-- WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.JkWaktuJatuhTempo AS DATE)) BETWEEN 361 AND 540
-- ),0)
, Year2 =
ISNULL((
select Nominal * @2Y from '+@mainTable+'
),0)
, Year3 =
ISNULL((
select Nominal * @3Y from '+@mainTable+'
),0)
, Year4 =
ISNULL((
select Nominal * @4Y from '+@mainTable+'
),0)
, Year5 =
ISNULL((
select Nominal * @5Y from '+@mainTable+'
),0)
, Year6 =
ISNULL((
select Nominal * @6Y from '+@mainTable+'
),0)
, Year7 =
ISNULL((
select Nominal * @7Y from '+@mainTable+'
),0)
, Year8 =
ISNULL((
select Nominal * @8Y from '+@mainTable+'
),0)
, Year9 =
ISNULL((
select Nominal * @9Y from '+@mainTable+'
),0)
, Year10 =
ISNULL((
select Nominal * @10Y from '+@mainTable+'
),0)
, Year15 =
ISNULL((
select Nominal * @11YAbove from '+@mainTable+'
),0);';
set @sql = @sql + '
SELECT Nominal AS ColumnValue FROM '+@mainTable+'
UNION ALL
SELECT CAST(PortofolioRate AS DECIMAL(10, 2)) FROM '+@mainTable+'
UNION ALL
SELECT Overnight FROM '+@mainTable+'
UNION ALL
SELECT Month1 FROM '+@mainTable+'
UNION ALL
SELECT Month2 FROM '+@mainTable+'
UNION ALL
SELECT Month3 FROM '+@mainTable+'
UNION ALL
SELECT Month4 FROM '+@mainTable+'
UNION ALL
SELECT Month5 FROM '+@mainTable+'
UNION ALL
SELECT Month6 FROM '+@mainTable+'
UNION ALL
SELECT Month7 FROM '+@mainTable+'
UNION ALL
SELECT Month8 FROM '+@mainTable+'
UNION ALL
SELECT Month9 FROM '+@mainTable+'
UNION ALL
SELECT Month10 FROM '+@mainTable+'
UNION ALL
SELECT Month11 FROM '+@mainTable+'
UNION ALL
SELECT Month12 FROM '+@mainTable+'
UNION ALL
SELECT Month18 FROM '+@mainTable+'
UNION ALL
SELECT Year2 FROM '+@mainTable+'
UNION ALL
SELECT Year3 FROM '+@mainTable+'
UNION ALL
SELECT Year4 FROM '+@mainTable+'
UNION ALL
SELECT Year5 FROM '+@mainTable+'
UNION ALL
SELECT Year6 FROM '+@mainTable+'
UNION ALL
SELECT Year7 FROM '+@mainTable+'
UNION ALL
SELECT Year8 FROM '+@mainTable+'
UNION ALL
SELECT Year9 FROM '+@mainTable+'
UNION ALL
SELECT Year10 FROM '+@mainTable+'
UNION ALL
SELECT Year15 FROM '+@mainTable+'
UNION ALL
SELECT Year20 FROM '+@mainTable+'
UNION ALL
SELECT Year20Plus FROM '+@mainTable+'
DROP TABLE IF EXISTS '+@mainTable+';
DROP TABLE IF EXISTS '+@masterTable+';
';
exec (@sql)
END;
Editor is loading...
Leave a Comment