bhvr_equation_deposito_non_aro
unknown
sqlserver
10 months ago
9.1 kB
2
Indexable
USE [bsi]
GO
/****** Object: StoredProcedure [mrms].[bhvr_equation_depositoNonAro] Script Date: 26/02/2024 09:32:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: WILLY ANGGARA
-- Create date: 17 Jul 2023
-- Description: DEPOSITO NON ARO
-- =============================================
ALTER PROCEDURE [mrms].[bhvr_equation_depositoNonAro]
-- Add the parameters for the stored procedure here
@beginDateString varchar(10),
@endDateString varchar(10),
@filterDinamic varchar (max),
@isDeleted int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @beginDate datetime = (SELECT convert(datetime, @beginDateString, 103))
declare @endDate datetime = (SELECT convert(datetime, @endDateString, 103))
declare @dateString varchar(8) = (select CONVERT(CHAR(8),EOMonth(@beginDate),112))
set @filterDinamic = replace (@filterDinamic,'[masterDPK_BSI]','a')
create table #PeriodeMasterDPK (periode varchar (100))
declare @reportDate datetime = @begindate;
while @reportDate <=@enddate
begin
declare @periodestring varchar(20) = format(dbo.EOMLastDayWork(@reportDate),'yyyyMMdd');
insert into #PeriodeMasterDPK
select 'masterDPK_BSI_'+@periodestring+''
set @reportDate =DATEADD(MONTH,1,@reportDate)
end
IF OBJECT_ID('tempdb..#periodeTableList') IS NOT NULL
DROP TABLE #periodeTableList
create table #periodeTableList (tableName varchar (30),periode varchar(8), periodeBefore varchar(8))
insert into #periodeTableList
select TABLE_NAME, CAST( replace (TABLE_NAME,'masterDPK_BSI_','') AS char(8)) periode,
CASE
WHEN ROW_NUMBER() OVER (ORDER BY TABLE_NAME) = 1 THEN
CONVERT(char(8), EOMONTH(DATEADD(MONTH, -1, CONVERT(DATE, STUFF(TABLE_NAME, 1, LEN('masterDPK_BSI_'), '')))), 112)
ELSE
CONVERT(char(8), LAG(CAST(CAST(REPLACE(TABLE_NAME, 'masterDPK_BSI_', '') AS char(8)) AS date), 1) OVER (ORDER BY TABLE_NAME), 112)
END AS periodeBefore
from INFORMATION_SCHEMA.TABLES a
inner join #PeriodeMasterDPK b on a.TABLE_NAME = b.periode
where TABLE_NAME like 'masterDPK_BSI%' and TABLE_SCHEMA = 'ddm'
declare @mainTableSql varchar (max)= '
create table #result ( periode datetime, tenor varchar (20))
insert into #result
SELECT dateadd(month,number,'''+cast (@beginDate as varchar)+''') periode,''01M'' tenor
FROM master..spt_values
WHERE
Type = ''P'' AND dateadd(month,number,'''+cast (@beginDate as varchar)+''') <= '''+cast (@endDate as varchar)+'''
union
SELECT dateadd(month,number,'''+cast (@beginDate as varchar)+''') periode,''03M'' tenor
FROM master..spt_values
WHERE
Type = ''P'' AND dateadd(month,number,'''+cast (@beginDate as varchar)+''') <= '''+cast (@endDate as varchar)+'''
union
SELECT dateadd(month,number,'''+cast (@beginDate as varchar)+''') periode,''06M'' tenor
FROM master..spt_values
WHERE
Type = ''P'' AND dateadd(month,number,'''+cast (@beginDate as varchar)+''') <= '''+cast (@endDate as varchar)+'''
union
SELECT dateadd(month,number,'''+cast (@beginDate as varchar)+''') periode,''12M'' tenor
FROM master..spt_values
WHERE
Type = ''P'' AND dateadd(month,number,'''+cast (@beginDate as varchar)+''') <= '''+cast (@endDate as varchar)+'''
'
if @isDeleted = 1
begin
declare @nonActiveResult varchar (max)= @mainTableSql + '
select b.currencyType, DATEFROMPARTS(YEAR(a.periode),MONTH(a.periode ),1) periode , ''DEPO NON ARO'' jenis,tenor, 0.0 retailMature,0.0 retailNotMature,0.0 corporateMature,0.0 corporateNotMature
from #result a
cross join (
select ''IDR'' currencyType union select ''VALAS'' currencyType
)b
order by b.currencyType,a.periode
'
exec (@nonActiveResult)
return
end
declare @sql varchar (max) = '' + @mainTableSql
set @sql = replace(@sql,'{filter}',case when @filterDinamic <> '' then 'and '+ @filterDinamic else '' end )
set @sql = replace(@sql,'masterDPK_BSI','masterDPK_BSI_'+@dateString)
set @sql = replace(@sql,'ds_az_account_n_eom','ds_az_account_n_eom_'+@dateString)
select @sql = COALESCE(@sql,' ') +
'SELECT
ResultZero.currencyType,
ResultZero.periode,
ResultZero.jenis,
ResultZero.tenor,
CASE WHEN Calc.retailMature > 0 THEN Calc.retailMature ELSE ResultZero.retailMature END retailMature,
CASE WHEN Calc.retailNotMature > 0 THEN Calc.retailNotMature ELSE ResultZero.retailNotMature END retailNotMature,
CASE WHEN Calc.corporateMature > 0 THEN Calc.corporateMature ELSE ResultZero.corporateMature END corporateMature,
CASE WHEN Calc.corporateNotMature > 0 THEN Calc.corporateNotMature ELSE ResultZero.corporateNotMature END corporateNotMature
FROM
(SELECT
b.currencyType,
DATEFROMPARTS(YEAR(a.periode), MONTH(a.periode), 1) periode,
''DEPO NON ARO'' jenis,
tenor,
0.0 retailMature,
0.0 retailNotMature,
0.0 corporateMature,
0.0 corporateNotMature
FROM #result a
CROSS JOIN (
SELECT ''IDR'' currencyType UNION SELECT ''VALAS'' currencyType
) b
WHERE periode = DATEFROMPARTS(YEAR(cast ('''+periode+ ''' as date) ),MONTH(cast ('''+periode+ ''' as date) ),1)) ResultZero
LEFT JOIN
(
SELECT
case when MDPK_BSI.jenisValuta != ''IDR'' then ''VALAS'' else MDPK_BSI.jenisValuta end currencyType,
DATEFROMPARTS(YEAR(cast ('''+periode+ ''' as date) ),MONTH(cast ('''+periode+ ''' as date) ),1) periode,
''DEPO NON ARO'' jenis,
MDPK_BSI.jangkaWaktu tenor,
ISNULL(SUM(CASE WHEN TRIM(MDPK_BSI.KodeBU) IN (''MBG'', ''SMG'', ''FHG'') AND MDPK_BSI.JkWaktuJatuhTempo >= DATEFROMPARTS(YEAR(cast ('''+periode+ ''' as date) ),MONTH(cast ('''+periode+ ''' as date) ),1) AND MDPK_BSI.JkWaktuJatuhTempo <= EOMONTH(CAST('''+periode+ ''' AS DATE)) THEN MDPK_BSI.saldoLSMK ELSE 0 END), 0) retailMature,
ISNULL(SUM(CASE WHEN TRIM(MDPK_BSI.KodeBU) IN (''MBG'', ''SMG'', ''FHG'') AND MDPK_BSI_MC.noCIF IS NOT NULL AND MDPK_BSI.JkWaktuJatuhTempo >= DATEFROMPARTS(YEAR(cast ('''+periode+ ''' as date) ),MONTH(cast ('''+periode+ ''' as date) ),1) AND MDPK_BSI.JkWaktuJatuhTempo <= EOMONTH(CAST('''+periode+ ''' AS DATE)) THEN MDPK_BSI_MC.saldoLSMK ELSE 0 END), 0) retailNotMature,
--ISNULL(SUM(CASE WHEN TRIM(MDPK_BSI.KodeBU) IN (''MBG'', ''SMG'', ''FHG'') AND MDPK_BSI_MC.noCIF IS NOT NULL AND MDPK_BSI.JkWaktuJatuhTempo > EOMONTH(CAST('''+periode+ ''' AS DATE)) THEN MDPK_BSI.saldoLSMK ELSE 0 END), 0) retailNotMature,
ISNULL(SUM(CASE WHEN TRIM(MDPK_BSI.KodeBU) IN (''CB1G'', ''CB2G'', ''CB3G'', ''CMG'', ''IBG'', ''IBGKP'') AND MDPK_BSI.JkWaktuJatuhTempo >= DATEFROMPARTS(YEAR(cast ('''+periode+ ''' as date) ),MONTH(cast ('''+periode+ ''' as date) ),1) AND MDPK_BSI.JkWaktuJatuhTempo <= EOMONTH(CAST('''+periode+ ''' AS DATE)) THEN MDPK_BSI.saldoLSMK ELSE 0 END), 0) corporateMature,
ISNULL(SUM(CASE WHEN TRIM(MDPK_BSI.KodeBU) IN (''CB1G'', ''CB2G'', ''CB3G'', ''CMG'', ''IBG'', ''IBGKP'') AND MDPK_BSI_MC.noCIF IS NOT NULL AND MDPK_BSI.JkWaktuJatuhTempo >= DATEFROMPARTS(YEAR(cast ('''+periode+ ''' as date) ),MONTH(cast ('''+periode+ ''' as date) ),1) AND MDPK_BSI.JkWaktuJatuhTempo <= EOMONTH(CAST('''+periode+ ''' AS DATE)) THEN MDPK_BSI_MC.saldoLSMK ELSE 0 END), 0) corporateNotMature
--ISNULL(SUM(CASE WHEN TRIM(MDPK_BSI.KodeBU) IN (''CB1G'', ''CB2G'', ''CB3G'', ''CMG'', ''IBG'', ''IBGKP'') AND MDPK_BSI_MC.noCIF IS NOT NULL AND MDPK_BSI.JkWaktuJatuhTempo > EOMONTH(CAST('''+periode+ ''' AS DATE)) THEN MDPK_BSI.saldoLSMK ELSE 0 END), 0) corporateNotMature
FROM [ddm].[masterDPK_BSI_'+periodeBefore+'] MDPK_BSI WITH (NOLOCK)
LEFT JOIN [mis].[ds_az_account_n_eom_'+periodeBefore+'] a WITH (NOLOCK) ON MDPK_BSI.noRekening = a.ACCOUNT_NUMBER
LEFT JOIN [ddm].[masterDPK_BSI_'+periode+'] MDPK_BSI_MC with (nolock)
on MDPK_BSI.noRekening = MDPK_BSI_MC.noRekening
WHERE MDPK_BSI.jenisProduk = ''Deposito''
AND MDPK_BSI.jangkaWaktu IS NOT NULL AND MDPK_BSI.jangkaWaktu IN (''01M'', ''03M'', ''06M'', ''12M'')
AND (a.ARO_OPTION IS NULL OR a.ARO_OPTION = '''' OR LTRIM(RTRIM(a.ARO_OPTION)) = '''')
group by case when MDPK_BSI.jenisValuta != ''IDR'' then ''VALAS'' else MDPK_BSI.jenisValuta end, MDPK_BSI.jangkaWaktu
) Calc
ON Calc.currencyType = ResultZero.currencyType AND Calc.tenor = ResultZero.tenor
union all
' from #periodeTableList
set @sql = replace(@sql,'{filter}',case when @filterDinamic <> '' then 'and '+ @filterDinamic else '' end )
set @sql = substring (@sql,1,len(@sql)-13)
set @sql = @sql + ' order by ResultZero.currencyType , ResultZero.periode, ResultZero.tenor'
--print(@sql)
--select * from #periodeTableList
exec (@sql)
END
/*
exec [mrms].[bhvr_equation_depositoNonAro] '01-06-2021', '01-05-2022','',0
exec [mrms].[bhvr_equation_depositoAro] '01-06-2021', '01-05-2022','',0
*/
Leave a Comment