bhvr_equation_deposito_aro

mail@pastecode.io avatar
unknown
sqlserver
2 months ago
9.1 kB
2
Indexable
Never
USE [bsi]
GO
/****** Object:  StoredProcedure [mrms].[bhvr_equation_depositoAro]    Script Date: 26/02/2024 09:31:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		WILLY ANGGARA
-- Create date: 17 Jul 2023
-- Description:	DEPOSITO ARO
-- =============================================
ALTER PROCEDURE [mrms].[bhvr_equation_depositoAro]
	-- 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 (100),periode varchar(20), periodeBefore varchar(20))
	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), dbo.EOMLastDayWork(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 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 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 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.noRekening 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.noRekening 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.noRekening 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.noRekening 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 NOT NULL AND a.ARO_OPTION <> '''' AND LTRIM(RTRIM(a.ARO_OPTION)) <> '''' AND CAST(LTRIM(RTRIM(a.ARO_OPTION)) AS DECIMAL) > 0)
					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_depositoAro] '01-06-2021', '01-05-2022','',0
exec [mrms].[bhvr_equation_depositoAro] '01-06-2022', '01-06-2023','',0
*/

Leave a Comment