SP Deposito

mail@pastecode.io avatar
unknown
plain_text
7 months ago
17 kB
5
Indexable
Never
USE [bsi]
GO
/****** Object:  StoredProcedure [mrms].[IRRBB_Deposito]    Script Date: 20/12/2023 15:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	ALTER PROCEDURE [mrms].[IRRBB_Deposito]
	  @reportDate VARCHAR(10),
	  @code VARCHAR(100),
	  @filterQuery VARCHAR(max),
	  @randomString VARCHAR(max)
	AS
	/*dihapus jika ingin create SP, hanya untuk testing hasil keluarannya*/
	--===============================================================================
	--DECLARE @reportDate VARCHAR(10) = '20230630';
	--DECLARE @code VARCHAR(100) = 'DEP_1_IDR';
	--DECLARE @filterQuery VARCHAR(max) = '
	--[ddm].[masterDPK_BSI_20230630].jangkaWaktu = ''01M'' 
	--AND [ddm].[masterDPK_BSI_20230630].jenisProduk = ''Deposito'' 
	--AND [ddm].[masterDPK_BSI_20230630].JenisValuta = ''IDR'' 
	--';
	--DECLARE @randomString VARCHAR(max) = 'random';
	--==============================================================================
	BEGIN
		declare @sql varchar (max) ='';
		declare @mainTable varchar(max) = '#TempRepricingGap_'+@randomString;
		declare @masterTable varchar(max) = '#TempQueryFilter_'+@randomString;
		
		DECLARE @overnight INT = 1;
		DECLARE @1MonthDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 1, @reportDate)) + 1, 0)));
		DECLARE @2MonthDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 2, @reportDate)) + 1, 0)));
		DECLARE @3MonthDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 3, @reportDate)) + 1, 0)));
		DECLARE @4MonthDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 4, @reportDate)) + 1, 0)));
		DECLARE @5MonthDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 5, @reportDate)) + 1, 0)));
		DECLARE @6MonthDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 6, @reportDate)) + 1, 0)));
		DECLARE @7MonthDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 7, @reportDate)) + 1, 0)));
		DECLARE @8MonthDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 8, @reportDate)) + 1, 0)));
		DECLARE @9MonthDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 9, @reportDate)) + 1, 0)));
		DECLARE @10MonthDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 10, @reportDate)) + 1, 0)));
		DECLARE @11MonthDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 11, @reportDate)) + 1, 0)));
		DECLARE @12MonthDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 12, @reportDate)) + 1, 0)));
		DECLARE @18MonthDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 18, @reportDate)) + 1, 0)));
		DECLARE @2YearDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 24, @reportDate)) + 1, 0)));
		DECLARE @3YearDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 36, @reportDate)) + 1, 0)));
		DECLARE @4YearDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 48, @reportDate)) + 1, 0)));
		DECLARE @5YearDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 60, @reportDate)) + 1, 0)));
		DECLARE @6YearDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 72, @reportDate)) + 1, 0)));
		DECLARE @7YearDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 84, @reportDate)) + 1, 0)));
		DECLARE @8YearDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 96, @reportDate)) + 1, 0)));
		DECLARE @9YearDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 108, @reportDate)) + 1, 0)));
		DECLARE @10YearDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 120, @reportDate)) + 1, 0)));
		DECLARE @15YearDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 180, @reportDate)) + 1, 0)));
		DECLARE @20YearDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 240, @reportDate)) + 1, 0)));
		DECLARE @25YearDiff INT = DATEDIFF(DAY, @reportDate, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, 300, @reportDate)) + 1, 0)));

		set @sql = '
		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+'
		  AND CAST(jkWaktuJatuhTempo AS DATE) > CAST('''+@reportDate+''' AS DATE) ;

		 --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--
		 With xTblTimeBucket_'+@randomString+' AS
		 (
			SELECT saldoLSMK Amount, MaturityDate
			FROM '+@masterTable+' A
			Where CAST(MaturityDate AS DATE) > CAST('''+@reportDate+''' AS DATE)
		 )
		 UPDATE '+@mainTable+' 
			SET 
			Overnight = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) = '+CAST(@overnight AS VARCHAR(10))+'
				),0)
			, Month1 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@overnight AS VARCHAR(10))+' + 1 AND '+CAST(@1MonthDiff AS VARCHAR(10))+'
				),0)
			, Month2 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@1MonthDiff AS VARCHAR(10))+' + 1 AND '+CAST(@2MonthDiff AS VARCHAR(10))+'
				),0)
			, Month3 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@2MonthDiff AS VARCHAR(10))+' + 1 AND '+CAST(@3MonthDiff AS VARCHAR(10))+'
				),0)
			, Month4 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@3MonthDiff AS VARCHAR(10))+' + 1 AND '+CAST(@4MonthDiff AS VARCHAR(10))+'
				),0)
			, Month5 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@4MonthDiff AS VARCHAR(10))+' + 1 AND '+CAST(@5MonthDiff AS VARCHAR(10))+'
				),0)
			, Month6 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@5MonthDiff AS VARCHAR(10))+' + 1 AND '+CAST(@6MonthDiff AS VARCHAR(10))+'
				),0)
			, Month7 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@6MonthDiff AS VARCHAR(10))+' + 1 AND '+CAST(@7MonthDiff AS VARCHAR(10))+'
				),0)
			, Month8 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@7MonthDiff AS VARCHAR(10))+' + 1 AND '+CAST(@8MonthDiff AS VARCHAR(10))+'
				),0)
			, Month9 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@8MonthDiff AS VARCHAR(10))+' + 1 AND '+CAST(@9MonthDiff AS VARCHAR(10))+'
				),0)
			, Month10 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@9MonthDiff AS VARCHAR(10))+' + 1 AND '+CAST(@10MonthDiff AS VARCHAR(10))+'
				),0)
			, Month11 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@10MonthDiff AS VARCHAR(10))+' + 1 AND '+CAST(@11MonthDiff AS VARCHAR(10))+'
				),0)
			, Month12 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@11MonthDiff AS VARCHAR(10))+' + 1 AND '+CAST(@12MonthDiff AS VARCHAR(10))+'
				),0)
			, Month18 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@12MonthDiff AS VARCHAR(10))+' + 1 AND '+CAST(@18MonthDiff AS VARCHAR(10))+'
				),0)
			, Year2 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@18MonthDiff AS VARCHAR(10))+' + 1 AND '+CAST(@2YearDiff AS VARCHAR(10))+'
				),0)
			, Year3 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@2YearDiff AS VARCHAR(10))+' + 1 AND '+CAST(@3YearDiff AS VARCHAR(10))+'
				),0)
			, Year4 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@3YearDiff AS VARCHAR(10))+' + 1 AND '+CAST(@4YearDiff AS VARCHAR(10))+'
				),0)
			, Year5 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@4YearDiff AS VARCHAR(10))+' + 1 AND '+CAST(@5YearDiff AS VARCHAR(10))+'
				),0)
			, Year6 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@5YearDiff AS VARCHAR(10))+' + 1 AND '+CAST(@6YearDiff AS VARCHAR(10))+'
				),0)
			, Year7 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@6YearDiff AS VARCHAR(10))+' + 1 AND '+CAST(@7YearDiff AS VARCHAR(10))+'
				),0)
			, Year8 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@7YearDiff AS VARCHAR(10))+' + 1 AND '+CAST(@8YearDiff AS VARCHAR(10))+'
				),0)
			, Year9 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@8YearDiff AS VARCHAR(10))+' + 1 AND '+CAST(@9YearDiff AS VARCHAR(10))+'
				),0)
			, Year10 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@9YearDiff AS VARCHAR(10))+' + 1 AND '+CAST(@10YearDiff AS VARCHAR(10))+'
				),0)
			, Year15 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@10YearDiff AS VARCHAR(10))+' + 1 AND '+CAST(@15YearDiff AS VARCHAR(10))+'
				),0)
			, Year20 = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) BETWEEN '+CAST(@15YearDiff AS VARCHAR(10))+' + 1 AND '+CAST(@20YearDiff AS VARCHAR(10))+'
				),0)
			, Year20Plus = 
				ISNULL((
					SELECT SUM(ISNULL(A.Amount, 0)) 
					FROM xTblTimeBucket_'+@randomString+' A
					WHERE DATEDIFF(DAY, CAST('''+@reportDate+''' AS DATE), CAST(A.MaturityDate AS DATE)) > '+CAST(@20YearDiff AS VARCHAR(10))+'
				),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;
Leave a Comment