bhvr_equation_deposito_non_aro
unknown
sqlserver
8 months ago
9.1 kB
2
Indexable
Never
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