-- ssp_ContributionAndGrowthPivot  null,null,null,null,null,null,'2016-04-01','2016-05-30','Month','Growth','Productwise'




ALTER PROC [dbo].[ssp_ContributionAndGrowthPivot]
 @ZCode INT,
 @RCode INT,     
 @SCode INT,    	   
 @SType INT,      
 @SCluster INT,    
 @SBrand INT,
 @FromDate DATETIME,
 @Todate DATETIME,
 @DateType VARCHAR(50),
 @ReportMode VARCHAR(50),                     
 @ReportType VARCHAR(50) 
 
 AS 
  BEGIN
 DECLARE @StartDate INT
 DECLARE @StartMonth INT
 DECLARE @EndDate VARCHAR(10)
 DECLARE @EndMonth VARCHAR(10)
 DECLARE @GrwthOvLY FLOAT
 DECLARE @SQL NVARCHAR(1000)
    IF @DateType='Year' 
	BEGIN

	IF   @ReportMode='Contribution' AND @ReportType='Brandwise' 
	BEGIN
	    

SELECT * INTO #Brandtemp FROM (
     SELECT      
      
            Z.ZoneName AS Zone,
            R.RegionName AS Region,
			S.AlternateStoreCode AS AlternateStoreCode,
			S.StoreName AS Store,    
			B.BrandName AS Brand,   
			S.StoreCode  , 			
			CONVERT(VARCHAR, YEAR(PSI.BillDate)) AS [SYear],					
			SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END) AS SaleValue
			--CAST(SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END)/(@@TBSaleVe) AS NUMERIC(15,2)) *100  AS [SaleGrowth%]			
			
	FROM  
	     
			--[dbo].[ffn_ProductsaleInfo](@FromMonth,@ToMonth,@SCode)    PSI
			Primary_ProductSaleInfo(@FromDate,@Todate,@ZCode,@RCode,@SCode,@SType,@SCluster,@SBrand,1,2,NULL,NULL)PSI
	INNER JOIN dbo.Store S WITH(NOLOCK) ON PSI.StoreCode = S.StoreCode    
	INNER JOIN dbo.Products P WITH(NOLOCK) ON PSI.ProductCode = P.ProductCode    
	INNER JOIN  dbo.Brand B WITH(NOLOCK) ON B.BrandCode = P.BrandCode
	INNER JOIN dbo.Region R WITH(NOLOCK) ON R.RegionCode = S.RegionCode     
	INNER JOIN  dbo.Zone Z WITH(NOLOCK) ON Z.ZoneCode=R.ZoneCode
	
			
WHERE    
			    (P.Classification = 0) 
			AND (PSI.BillDate BETWEEN @FromDate AND @Todate) 
			AND (PSI.Transactionvalidity = 0) 
			AND (S.StoreCode = @SCode OR @SCode IS NULL)  
			AND (S.StoreCluster= @SCluster OR @SCluster IS NULL)  
			AND (S.StoreBrand= @SBrand OR @SBrand IS NULL)  
			AND (R.RegionCode= @RCode OR @RCode IS NULL)  
			AND (S.StoreType= @SType OR @SType IS NULL)
			AND (R.ZoneCode = @ZCode OR @ZCode IS NULL)  
			AND (PSI.TransID IN (1,2))     
GROUP BY     

			Z.ZoneName,
			S.StoreName, 
			S.AlternateStoreCode,
			R.RegionName , 
			S.StoreCode, 
			--PSI.BillDate,  
			CONVERT(VARCHAR, YEAR(PSI.BillDate)),
			B.BrandName) X		

			--select * from #Brandtemp
			SELECT t.*,CAST((ISNULL(t.Salevalue,0)/CASE WHEN ISNULL(S.Total,0)=0 THEN 1 ELSE S.Total END) AS NUMERIC(16,2)) *100 AS [SaleGrowth%] FROM  #Brandtemp t left join 

			(SELECT SUM(saleValue)AS total,[SYear],StoreCode FROM #Brandtemp GROUP BY [SYear],StoreCode) S ON S.[SYear]=t.[SYear] and t.StoreCode=S.StoreCode
			END
 IF  @ReportMode='Contribution' AND @ReportType='Categorywise'
 BEGIN
 SELECT * INTO #Categtemp FROM (
     SELECT     
	        Z.ZoneName AS Zone,
            R.RegionName AS Region,
			S.AlternateStoreCode AS AlternateStoreCode,
			S.StoreName AS Store,
			S.StoreCode,
			CM.Description AS Category,
			B.BrandName AS Brand,    
			--CONVERT(VARCHAR,PSI.BillDate , 103) AS MonthAndYear,  
			CONVERT(VARCHAR, YEAR(PSI.BillDate)) AS [SYear],
			SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END) AS SaleValue--,
			--CAST(SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END)/(@@TCSaleVe)AS NUMERIC(15,2))*100  AS [SaleGrowth%]
	FROM 
			Primary_ProductSaleInfo(@FromDate,@Todate,@ZCode,@RCode,@SCode,@SType,@SCluster,@SBrand,1,2,NULL,NULL)PSI
	INNER JOIN dbo.Store S WITH(NOLOCK) ON PSI.StoreCode = S.StoreCode    
	INNER JOIN dbo.Products P WITH(NOLOCK) ON PSI.ProductCode = P.ProductCode    
	INNER JOIN dbo.CategoryMaster CM WITH(NOLOCK) ON CM.CategoryCode=P.CategoryCode
	INNER JOIN  dbo.Brand B WITH(NOLOCK) ON P.BrandCode = B.BrandCode
	INNER JOIN dbo.Region R WITH(NOLOCK) ON R.RegionCode = S.RegionCode     
	INNER JOIN  dbo.Zone Z WITH(NOLOCK) ON Z.ZoneCode=R.ZoneCode	
			
   WHERE    
			    (P.Classification = 0) 
			AND (PSI.BillDate BETWEEN @FromDate AND @Todate) 
			AND (PSI.Transactionvalidity = 0) 
			AND (S.StoreCode = @SCode OR @SCode IS NULL)  
			AND (S.StoreCluster= @SCluster OR @SCluster IS NULL)  
			AND (S.StoreBrand= @SBrand OR @SBrand IS NULL)  
			AND (R.RegionCode= @RCode OR @RCode IS NULL)  
			AND (S.StoreType= @SType OR @SType IS NULL)
			AND (R.ZoneCode = @ZCode OR @ZCode IS NULL)  
			AND (PSI.TransID IN (1,2))     
   GROUP BY     

			Z.ZoneName,
			S.StoreName, 
			S.AlternateStoreCode,
			R.RegionName , 
			S.StoreCode, 
			--PSI.BillDate,  
			CONVERT(VARCHAR, YEAR(PSI.BillDate)),
			CM.Description,
			B.BrandName) Y
			
			SELECT t.*,CAST((ISNULL(t.Salevalue,0)/CASE WHEN ISNULL(S.Total,0)=0 THEN 1 ELSE S.Total END) AS NUMERIC(16,2))*100 AS [SaleGrowth%] FROM  #Categtemp t left join 

			(SELECT Sum(saleValue)AS total,[SYear],StoreCode FROM #Categtemp GROUP BY [SYear],StoreCode) S ON S.[SYear]=t.[SYear] and t.StoreCode=S.StoreCode			  
			END
 IF  @ReportMode='Contribution' AND @ReportType='Productwise'
 BEGIN
 SELECT * INTO #Prodtemp FROM (
     SELECT     
	        Z.ZoneName AS Zone,
            R.RegionName AS Region,
			S.AlternateStoreCode AS AlternateStoreCode,
			S.StoreName AS Store,
			P.ProductName As PRODLINE,
			B.BrandName AS Brand,
			S.StoreCode,    
			--CONVERT(VARCHAR,PSI.BillDate , 103) AS MonthAndYear,  
			CONVERT(VARCHAR, YEAR(PSI.BillDate)) AS [SYear],
			SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END) AS SaleValue--,
			--CAST(SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END)/(@@TBSaleVe) AS NUMERIC(15,2)) *100  AS [SaleGrowth%]			
			FROM 
			Primary_ProductSaleInfo(@FromDate,@Todate,@ZCode,@RCode,@SCode,@SType,@SCluster,@SBrand,1,2,NULL,NULL)PSI
	INNER JOIN dbo.Store S WITH(NOLOCK) ON PSI.StoreCode = S.StoreCode    
	INNER JOIN dbo.Products P WITH(NOLOCK) ON PSI.ProductCode = P.ProductCode    	
	INNER JOIN  dbo.Brand B WITH(NOLOCK) ON P.BrandCode = B.BrandCode
	INNER JOIN dbo.Region R WITH(NOLOCK) ON R.RegionCode = S.RegionCode     
	INNER JOIN  dbo.Zone Z WITH(NOLOCK) ON Z.ZoneCode=R.ZoneCode	
			
WHERE    
			    (P.Classification = 0) 
			AND (PSI.BillDate BETWEEN @FromDate AND @Todate) 
			AND (PSI.Transactionvalidity = 0) 
			AND (S.StoreCode = @SCode OR @SCode IS NULL)  
			AND (S.StoreCluster= @SCluster OR @SCluster IS NULL)  
			AND (S.StoreBrand= @SBrand OR @SBrand IS NULL)  
			AND (R.RegionCode= @RCode OR @RCode IS NULL)  
			AND (S.StoreType= @SType OR @SType IS NULL)
			AND (R.ZoneCode = @ZCode OR @ZCode IS NULL)  
			AND (PSI.TransID IN (1,2))     
GROUP BY     

			Z.ZoneName,
			S.StoreName, 
			S.AlternateStoreCode,
			R.RegionName , 
			S.StoreCode,  
			--PSI.BillDate,  
			CONVERT(VARCHAR, YEAR(PSI.BillDate)),
			B.BrandName,
			P.ProductName) Z
			SELECT t.*,CAST((ISNULL(t.Salevalue,0)/CASE WHEN ISNULL(S.Total,0)=0 THEN 1 ELSE S.Total END) AS NUMERIC(16,2))*100 AS [SaleGrowth%] FROM  #Prodtemp t left join 

			(SELECT SUM(saleVAlue)AS total,[SYear],StoreCode FROM #Prodtemp GROUP BY [SYear],StoreCode) S ON S.[SYear]=t.[SYear]   and  t.StoreCode=S.StoreCode	

			END
ElSE IF @ReportMode='Growth' AND @ReportType='Brandwise' 

BEGIN

CREATE TABLE #BrandTempG (Zone VARCHAR(100),Region VARCHAR(100),StoreCode INT,AlternateStoreCode VARCHAR(50),
						   Store VARCHAR(150),Brand VARCHAR(100),SYear VARCHAR(10),SaleValue FLOAT)
INSERT	#BrandTempG    

   SELECT    
            Z.ZoneName AS Zone,
            R.RegionName AS Region,
			S.StoreCode AS StoreCode,
			S.AlternateStoreCode AS AlternateStoreCode,
			S.StoreName AS Store,    
			B.BrandName AS Brand,      
			--CONVERT(VARCHAR,PSI.BillDate , 103) AS MonthAndYear,  
			CONVERT(VARCHAR, YEAR(PSI.BillDate)) AS [SYear],
			--DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate) AS [MON-Year],
			SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END) AS SaleValue
			--CAST(SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END)/(@@TBSaleVe) AS NUMERIC(15,2)) *100  AS [SaleGrowth%]			
			    
	FROM    
			--[dbo].[ffn_ProductsaleInfo](@FromMonth,@ToMonth,@SCode)    PSI
			Primary_ProductSaleInfo(@FromDate,@Todate,@ZCode,@RCode,@SCode,@SType,@SCluster,@SBrand,1,2,NULL,NULL)PSI
	INNER JOIN dbo.Store S WITH(NOLOCK) ON PSI.StoreCode = S.StoreCode    
	INNER JOIN dbo.Products P WITH(NOLOCK) ON PSI.ProductCode = P.ProductCode    
	INNER JOIN  dbo.Brand B WITH(NOLOCK) ON P.BrandCode = B.BrandCode
	INNER JOIN dbo.Region R WITH(NOLOCK) ON R.RegionCode = S.RegionCode     
	INNER JOIN  dbo.Zone Z WITH(NOLOCK) ON Z.ZoneCode=R.ZoneCode
	
			
WHERE    
			    (P.Classification = 0) 
			AND (PSI.BillDate BETWEEN @FromDate AND @Todate) 
			AND (PSI.Transactionvalidity = 0) 
			AND (S.StoreCode = @SCode OR @SCode IS NULL)  
			AND (S.StoreCluster= @SCluster OR @SCluster IS NULL)  
			AND (S.StoreBrand= @SBrand OR @SBrand IS NULL)  
			AND (R.RegionCode= @RCode OR @RCode IS NULL)  
			AND (S.StoreType= @SType OR @SType IS NULL)
			AND (R.ZoneCode = @ZCode OR @ZCode IS NULL)  
			AND (PSI.TransID IN (1,2))     
GROUP BY     

			Z.ZoneName,
			S.StoreName,
			S.StoreCode, 
			S.AlternateStoreCode,
			R.RegionName ,  
			PSI.BillDate,  
			CONVERT(VARCHAR, YEAR(PSI.BillDate)),
			--DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate),
			B.BrandName


    SET @StartDate=YEAR(@FromDate);
	SET @EndDate = YEAR(@ToDate); 
	SET @SQL ='ALTER TABLE #BrandTempG ADD [SaleGrowth%] FLOAT';  
	EXEC SP_EXECUTESQL @SQL
	
	WHILE(@StartDate<@EndDate)
	BEGIN

	SET @SQL= ' UPDATE #BrandTempG  SET [SaleGrowth%] =0'
	        EXEC SP_EXECUTESQL @SQL
			SELECT      #BrandTempG.Zone,
			            #BrandTempG.Region,
						#BrandTempG.Store,
						#BrandTempG.StoreCode,
						#BrandTempG.AlternateStoreCode,
						#BrandTempG.SYear,
						#BrandTempG.Brand,
						--#BrandTempG.[MON-Year],
						ISNULL(PS.SaleValue,0) AS PSaleValue,
						ISNULL(NS.SaleValue,0) AS NSaleValue,					
					    CAST(CASE WHEN ISNULL(PS.SaleValue,0)=0 THEN 0
							 WHEN ISNULL(PS.SaleValue,0)<>0 THEN (((ISNULL(NS.SaleValue,0)-ISNULL(PS.SaleValue,0))/CASE WHEN ISNULL(PS.SaleValue,0)=0 THEN 1 ELSE PS.SaleValue END )*100)
						     WHEN ISNULL(NS.SaleValue,0)=0 THEN 100
							  END AS NUMERIC(16,2))  AS [SaleGrowth%]			   
				INTO #TempGrowth
				FROM
				      #BrandTempG 
	                LEFT JOIN 
						(
							SELECT S.StoreCode,
								   S.SaleValue
								  
							FROM
								#BrandTempG S
							WHERE S.SYear = @StartDate  AND S.SaleValue <> 0 
						) AS PS ON PS.StoreCode=#BrandTempG.StoreCode
				
						LEFT JOIN
						(
							SELECT S.StoreCode,
								   S.SaleValue
								  
							FROM
								#BrandTempG S
							WHERE S.SYear = @StartDate+1  AND S.SaleValue <> 0
						) AS NS ON NS.StoreCode=#BrandTempG.StoreCode
						
						SET @SQL=''
						SET @SQL= ' UPDATE TS SET TS.[SaleGrowth%] = TP.[SaleGrowth%]'
						SET @SQL= @SQL+ ' FROM #BrandTempG TS '	
						SET @SQL= @SQL+ ' INNER JOIN  #TempGrowth TP ON TP.StoreCode=TS.StoreCode'	
						SET @SQL= @SQL+ ' WHERE TS.SYear='+CONVERT(NVARCHAR,@StartDate+1)+''
							EXEC SP_EXECUTESQL @SQL

		                SET @StartDate=@StartDate+1;
					    DROP TABLE #TempGrowth
						 END
	    --SELECT Zone,Region,Store,AlternateStoreCode,Brand,SYear,SUM(SaleValue) AS SaleValue, SUM([SaleGrowth%]) [SaleGrowth%] FROM #BrandTempG group by syear,AlternateStoreCode,Zone,Region,Store,Brand order by SYear
	     SELECT * INTO #tempGrowthExact FROM(SELECT Zone,Region,Store,AlternateStoreCode,Brand,SYear,SUM(SaleValue) AS SaleValue, SUM([SaleGrowth%]) [SaleGrowth%] FROM #BrandTempG GROUP BY syear,AlternateStoreCode,Zone,Region,Store,Brand )X
		 SELECT * INTO #data FROM (
         SELECT Zone,Region,Store,AlternateStoreCode,Brand,SYear, SaleValue,
       LEAD(SaleValue, 1) OVER(PARTITION BY Store,Brand ORDER BY Store,Brand,SYear DESC) AS 'NextTransStart'   FROM #tempGrowthExact )Y  
	   SELECT  Zone,Region,Store,AlternateStoreCode,Brand,SYear, SaleValue,CAST(ISNULL(((salevalue-NextTransStart)/NextTransStart)*100,0)AS NUMERIC(15,2)) AS [SaleGrowth%] from #data
		

  END
IF @ReportMode='Growth' AND @ReportType='Categorywise' 
BEGIN

CREATE TABLE #CategTempG (Zone VARCHAR(100),Region VARCHAR(100),StoreCode INT,AlternateStoreCode VARCHAR(50),
						   Store VARCHAR(150),Category VARCHAR(100),SYear VARCHAR(10),SaleValue FLOAT)
INSERT	#CategTempG    

   SELECT    
            Z.ZoneName AS Zone,
            R.RegionName AS Region,
			S.StoreCode AS StoreCode,
			S.AlternateStoreCode AS AlternateStoreCode,
			S.StoreName AS Store,    
			CM.Description AS Category,      
			--CONVERT(VARCHAR,PSI.BillDate , 103) AS MonthAndYear,  
			CONVERT(VARCHAR, YEAR(PSI.BillDate)) AS [SYear],
			--DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate) AS [MON-Year],
			SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END) AS SaleValue
			--CAST(SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END)/(@@TBSaleVe) AS NUMERIC(15,2)) *100  AS [SaleGrowth%]			
			    
	FROM    
			--[dbo].[ffn_ProductsaleInfo](@FromMonth,@ToMonth,@SCode)    PSI
			Primary_ProductSaleInfo(@FromDate,@Todate,@ZCode,@RCode,@SCode,@SType,@SCluster,@SBrand,1,2,NULL,NULL)PSI
	INNER JOIN dbo.Store S WITH(NOLOCK) ON PSI.StoreCode = S.StoreCode    
	INNER JOIN dbo.Products P WITH(NOLOCK) ON PSI.ProductCode = P.ProductCode    
	INNER JOIN  dbo.Brand B WITH(NOLOCK) ON P.BrandCode = B.BrandCode
	INNER JOIN dbo.Region R WITH(NOLOCK) ON R.RegionCode = S.RegionCode     
	INNER JOIN  dbo.Zone Z WITH(NOLOCK) ON Z.ZoneCode=R.ZoneCode
	LEFT JOIN dbo.CategoryMaster CM WITH(NOLOCK) ON CM.CategoryCode=P.categoryCode
			
WHERE    
			    (P.Classification = 0) 
			AND (PSI.BillDate BETWEEN @FromDate AND @Todate) 
			AND (PSI.Transactionvalidity = 0) 
			AND (S.StoreCode = @SCode OR @SCode IS NULL)  
			AND (S.StoreCluster= @SCluster OR @SCluster IS NULL)  
			AND (S.StoreBrand= @SBrand OR @SBrand IS NULL)  
			AND (R.RegionCode= @RCode OR @RCode IS NULL)  
			AND (S.StoreType= @SType OR @SType IS NULL)
			AND (R.ZoneCode = @ZCode OR @ZCode IS NULL)  
			AND (PSI.TransID IN (1,2))     
GROUP BY     

			Z.ZoneName,
			S.StoreName,
			S.StoreCode, 
			S.AlternateStoreCode,
			R.RegionName ,  
			PSI.BillDate,  
			CONVERT(VARCHAR, YEAR(PSI.BillDate)),
			--DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate),
		    CM.Description

    SET @StartDate=YEAR(@FromDate);
	SET @EndDate = YEAR(@ToDate); 
	SET @SQL ='ALTER TABLE #CategTempG ADD [SaleGrowth%] FLOAT';  
	EXEC SP_EXECUTESQL @SQL
	
	WHILE(@StartDate<@EndDate)
	BEGIN

	SET @SQL= ' UPDATE #CategTempG  SET [SaleGrowth%] =0'
	        EXEC SP_EXECUTESQL @SQL
			SELECT      #CategTempG.Zone,
			            #CategTempG.Region,
						#CategTempG.Store,
					    #CategTempG.AlternateStoreCode,
						#CategTempG.StoreCode,
						#CategTempG.SYear,
						#CategTempG.Category,
						--#CategTempG.[MON-Year],
						ISNULL(PS.SaleValue,0) AS PSaleValue,
						ISNULL(NS.SaleValue,0) AS NSaleValue,					
					    CAST(CASE WHEN ISNULL(PS.SaleValue,0)=0 THEN 0
							 WHEN ISNULL(PS.SaleValue,0)<>0 THEN (((ISNULL(NS.SaleValue,0)-ISNULL(PS.SaleValue,0))/CASE WHEN ISNULL(PS.SaleValue,0)=0 THEN 1 ELSE PS.SaleValue END )*100)
						     WHEN ISNULL(NS.SaleValue,0)=0 THEN 100
							  END AS NUMERIC(16,2))  AS [SaleGrowth%]			   
				INTO #TempGrowth1
				FROM
				      #CategTempG 
	                LEFT JOIN 
						(
							SELECT S.StoreCode,
								   S.SaleValue
								  
							FROM
								#CategTempG S
							WHERE S.SYear = @StartDate  AND S.SaleValue <> 0 
						) AS PS ON PS.StoreCode=#CategTempG.StoreCode
				
						LEFT JOIN
						(
							SELECT S.StoreCode,
								   S.SaleValue
								  
							FROM
								#CategTempG S
							WHERE S.SYear = @StartDate+1  AND S.SaleValue <> 0
						) AS NS ON NS.StoreCode=#CategTempG.StoreCode
						
						SET @SQL=''
						SET @SQL= ' UPDATE TS SET TS.[SaleGrowth%] = TP.[SaleGrowth%]'
						SET @SQL= @SQL+ ' FROM #CategTempG TS '	
						SET @SQL= @SQL+ ' INNER JOIN  #TempGrowth1 TP ON TP.StoreCode=TS.StoreCode'	
						SET @SQL= @SQL+ ' WHERE TS.SYear='+CONVERT(NVARCHAR,@StartDate+1)+''
							EXEC SP_EXECUTESQL @SQL

		                SET @StartDate=@StartDate+1;
					    DROP TABLE #TempGrowth1
						 END
	    --SELECT Zone,Region,Store,AlternateStoreCode,Category,SYear,SUM(SaleValue) AS SaleValue,SUM([SaleGrowth%]) [SaleGrowth%] FROM #CategTempG group by syear,AlternateStoreCode,Zone,Region,Store,Category order by SYear
	      SELECT * INTO #tempGrowthExact1 FROM(SELECT Zone,Region,Store,AlternateStoreCode,Category,SYear,SUM(SaleValue) AS SaleValue,SUM([SaleGrowth%]) [SaleGrowth%] FROM #CategTempG group by syear,AlternateStoreCode,Zone,Region,Store,Category)X
		 SELECT * INTO #data1 FROM (
         SELECT Zone,Region,Store,AlternateStoreCode,Category,SYear, SaleValue,
       LEAD(SaleValue, 1) OVER(PARTITION BY Store,Category ORDER BY Store,Category,SYear DESC) AS 'NextTransStart'   FROM #tempGrowthExact1 )Y  
	   SELECT  Zone,Region,Store,AlternateStoreCode,Category,SYear, SaleValue,CAST(ISNULL(((salevalue-NextTransStart)/NextTransStart)*100,0)AS NUMERIC(15,2)) AS [SaleGrowth%] from #data1
		 --DROP TABLE #CategTempG
		 
	END

IF  @ReportMode='Growth' AND @ReportType='Productwise'
 BEGIN
  CREATE TABLE #ProdTempG (Zone VARCHAR(100),Region VARCHAR(100),StoreCode INT,AlternateStoreCode VARCHAR(50),
						   Store VARCHAR(150),PRODLINE VARCHAR(100),SYear VARCHAR(10),SaleValue FLOAT)
INSERT	#ProdTempG    

   SELECT    
            Z.ZoneName AS Zone,
            R.RegionName AS Region,
			S.StoreCode AS StoreCode,
			S.AlternateStoreCode AS AlternateStoreCode,
			S.StoreName AS Store,    
			P.ProductName As PRODLINE,   
			--CONVERT(VARCHAR,PSI.BillDate , 103) AS MonthAndYear,  
			CONVERT(VARCHAR, YEAR(PSI.BillDate)) AS [SYear],
			--DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate) AS [MON-Year],
			SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END) AS SaleValue
			--CAST(SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END)/(@@TBSaleVe) AS NUMERIC(15,2)) *100  AS [SaleGrowth%]			
			    
	FROM    
			--[dbo].[ffn_ProductsaleInfo](@FromMonth,@ToMonth,@SCode)    PSI
			Primary_ProductSaleInfo(@FromDate,@Todate,@ZCode,@RCode,@SCode,@SType,@SCluster,@SBrand,1,2,NULL,NULL)PSI
	INNER JOIN dbo.Store S WITH(NOLOCK) ON PSI.StoreCode = S.StoreCode    
	INNER JOIN dbo.Products P WITH(NOLOCK) ON PSI.ProductCode = P.ProductCode    
	INNER JOIN  dbo.Brand B WITH(NOLOCK) ON P.BrandCode = B.BrandCode
	INNER JOIN dbo.Region R WITH(NOLOCK) ON R.RegionCode = S.RegionCode     
	INNER JOIN  dbo.Zone Z WITH(NOLOCK) ON Z.ZoneCode=R.ZoneCode
	LEFT JOIN dbo.CategoryMaster CM WITH(NOLOCK) ON CM.CategoryCode=P.categoryCode
			
WHERE    
			    (P.Classification = 0) 
			AND (PSI.BillDate BETWEEN @FromDate AND @Todate) 
			AND (PSI.Transactionvalidity = 0) 
			AND (S.StoreCode = @SCode OR @SCode IS NULL)  
			AND (S.StoreCluster= @SCluster OR @SCluster IS NULL)  
			AND (S.StoreBrand= @SBrand OR @SBrand IS NULL)  
			AND (R.RegionCode= @RCode OR @RCode IS NULL)  
			AND (S.StoreType= @SType OR @SType IS NULL)
			AND (R.ZoneCode = @ZCode OR @ZCode IS NULL)  
			AND (PSI.TransID IN (1,2))     
GROUP BY     

			Z.ZoneName,
			S.StoreName,
			S.StoreCode, 
			S.AlternateStoreCode,
			R.RegionName ,  
			PSI.BillDate,  
			CONVERT(VARCHAR, YEAR(PSI.BillDate)),
			--DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate),
		   P.ProductName

    SET @StartDate=YEAR(@FromDate);
	SET @EndDate = YEAR(@ToDate); 
	SET @SQL ='ALTER TABLE #ProdTempG ADD [SaleGrowth%] FLOAT';  
	EXEC SP_EXECUTESQL @SQL
	
	WHILE(@StartDate<@EndDate)
	BEGIN

	SET @SQL= ' UPDATE #ProdTempG SET [SaleGrowth%] =0'
	        EXEC SP_EXECUTESQL @SQL
			SELECT      #ProdTempG.Zone,
			            #ProdTempG.Region,
						#ProdTempG.Store,
						#ProdTempG.AlternateStoreCode,
						#ProdTempG.StoreCode,
						#ProdTempG.SYear,
						#ProdTempG.PRODLINE,
						--#CategTempG.[MON-Year],
						ISNULL(PS.SaleValue,0) AS PSaleValue,
						ISNULL(NS.SaleValue,0) AS NSaleValue,					
					    CAST(CASE WHEN ISNULL(PS.SaleValue,0)=0 THEN 0
							 WHEN ISNULL(PS.SaleValue,0)<>0 THEN (((ISNULL(NS.SaleValue,0)-ISNULL(PS.SaleValue,0))/CASE WHEN ISNULL(PS.SaleValue,0)=0 THEN 1 ELSE PS.SaleValue END )*100)
						     WHEN ISNULL(NS.SaleValue,0)=0 THEN 100
							  END AS NUMERIC(16,2))  AS [SaleGrowth%]			   
				INTO #TempGrowth2
				FROM
				      #ProdTempG
	                LEFT JOIN 
						(
							SELECT S.StoreCode,
								   S.SaleValue
								  
							FROM
								#ProdTempG S
							WHERE S.SYear = @StartDate  AND S.SaleValue <> 0 
						) AS PS ON PS.StoreCode=#ProdTempG.StoreCode
				
						LEFT JOIN
						(
							SELECT S.StoreCode,
								   S.SaleValue
								  
							FROM
								#ProdTempG S
							WHERE S.SYear = @StartDate+1  AND S.SaleValue <> 0
						) AS NS ON NS.StoreCode=#ProdTempG.StoreCode
						
						SET @SQL=''
						SET @SQL= ' UPDATE TS SET TS.[SaleGrowth%] = TP.[SaleGrowth%]'
						SET @SQL= @SQL+ ' FROM #ProdTempG TS '	
						SET @SQL= @SQL+ ' INNER JOIN  #TempGrowth2 TP ON TP.StoreCode=TS.StoreCode'	
						SET @SQL= @SQL+ ' WHERE TS.SYear='+CONVERT(NVARCHAR,@StartDate+1)+''
							EXEC SP_EXECUTESQL @SQL

		                SET @StartDate=@StartDate+1;
					    DROP TABLE #TempGrowth2
						 END
						  SELECT * INTO #tempGrowthExact2 FROM(SELECT Zone,Region,Store,AlternateStoreCode,PRODLINE,SYear,SUM(SaleValue) AS SaleValue,SUM([SaleGrowth%]) [SaleGrowth%] FROM #ProdTempG GROUP BY syear,AlternateStoreCode,Zone,Region,Store,PRODLINE)X
		 SELECT * INTO #data2 FROM (
         SELECT Zone,Region,Store,AlternateStoreCode,PRODLINE,SYear, SaleValue,
       LEAD(SaleValue, 1) OVER(PARTITION BY Store,PRODLINE ORDER BY Store,PRODLINE,SYear DESC) AS 'NextTransStart'   FROM #tempGrowthExact2 )Y  
	   SELECT  Zone,Region,Store,AlternateStoreCode,PRODLINE,SYear, SaleValue,CAST(ISNULL(((salevalue-NextTransStart)/NextTransStart)*100,0)AS NUMERIC(15,2)) AS [SaleGrowth%] from #data2
	    --SELECT Zone,Region,Store,AlternateStoreCode,PRODLINE,SYear,SUM(SaleValue) AS SaleValue,SUM([SaleGrowth%]) [SaleGrowth%] FROM #ProdTempG GROUP BY syear,AlternateStoreCode,Zone,Region,Store,PRODLINE ORDER BY SYear
	     --DROP TABLE #ProdTempG
END

END
ELSE IF @DateType='Month' 

BEGIN
IF  @ReportMode='Contribution' AND @ReportType='Brandwise' 
BEGIN

SELECT * INTO #BrandtempM FROM (
SELECT      Z.ZoneName AS Zone,
            R.RegionName AS Region,
			S.AlternateStoreCode AS AlternateStoreCode,
			S.StoreName AS Store, 
			S.StoreCode,   
			B.BrandName AS Brand,   	 
			CONVERT(VARCHAR, YEAR(PSI.BillDate)) AS [SYear] ,
			DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate) AS [MON-Year], 
			SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END) AS SaleValue--,
			--CAST(SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END)/(@@TBSaleVe) AS NUMERIC(15,2)) *100  AS [SaleGrowth%]			
	FROM    
			--[dbo].[ffn_ProductsaleInfo](@FromMonth,@ToMonth,@SCode)    PSI
			Primary_ProductSaleInfo(@FromDate,@Todate,@ZCode,@RCode,@SCode,@SType,@SCluster,@SBrand,1,2,NULL,NULL)PSI
	INNER JOIN dbo.Store S WITH(NOLOCK) ON PSI.StoreCode = S.StoreCode    
	INNER JOIN dbo.Products P WITH(NOLOCK) ON PSI.ProductCode = P.ProductCode    
	INNER JOIN  dbo.Brand B WITH(NOLOCK) ON P.BrandCode = B.BrandCode
	INNER JOIN dbo.Region R WITH(NOLOCK) ON R.RegionCode = S.RegionCode     
	INNER JOIN  dbo.Zone Z WITH(NOLOCK) ON Z.ZoneCode=R.ZoneCode
	
			
WHERE    
			    (P.Classification = 0) 
			AND (PSI.BillDate BETWEEN @FromDate AND @Todate) 
			AND (PSI.Transactionvalidity = 0) 
			AND (S.StoreCode = @SCode OR @SCode IS NULL)  
			AND (S.StoreCluster= @SCluster OR @SCluster IS NULL)  
			AND (S.StoreBrand= @SBrand OR @SBrand IS NULL)  
			AND (R.RegionCode= @RCode OR @RCode IS NULL)  
			AND (S.StoreType= @SType OR @SType IS NULL)
			AND (R.ZoneCode = @ZCode OR @ZCode IS NULL)  
			AND (PSI.TransID IN (1,2))     
GROUP BY     

			Z.ZoneName,
			S.StoreName, 
			S.AlternateStoreCode,
			R.RegionName , 
			S.StoreCode,   
			--PSI.BillDate,  
			CONVERT(VARCHAR, YEAR(PSI.BillDate)),
			DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate),
			Month(PSI.BillDate),
			B.BrandName) BM
			SELECT t.*,CAST((ISNULL(t.Salevalue,0)/CASE WHEN ISNULL(S.Total,0)=0 THEN 1 ELSE S.Total END) AS NUMERIC(15,2))*100 AS [SaleGrowth%] FROM  #BrandtempM t left join 
			(SELECT SUM(ISNULL(SaleValue,0)) AS total,StoreCode,  [MON-Year],Syear FROM #BrandtempM GROUP BY [MON-Year],Syear,StoreCode) S ON S.[MON-Year]=t.[MON-Year] AND S.Syear=t.Syear	AND S.StoreCode=t.StoreCode 
			END
IF  @ReportMode='Contribution' AND @ReportType='Categorywise'
 BEGIN
 SELECT * INTO #CategtempC FROM (
     SELECT     
	        Z.ZoneName AS Zone,
            R.RegionName AS Region,
			S.AlternateStoreCode AS AlternateStoreCode,
			S.StoreName AS Store,
			S.StoreCode,  
			CM.Description AS Category,
			B.BrandName AS Brand,    
			CONVERT(VARCHAR, YEAR(PSI.BillDate)) AS [SYear],
		    DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate) AS [MON-Year],  
			SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END) AS SaleValue--,
			--CAST(SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END)/(@@TCSaleVe) AS NUMERIC(15,2)) *100  AS [SaleGrowth%]			
			FROM 
			Primary_ProductSaleInfo(@FromDate,@Todate,@ZCode,@RCode,@SCode,@SType,@SCluster,@SBrand,1,2,NULL,NULL)PSI
	INNER JOIN dbo.Store S WITH(NOLOCK) ON PSI.StoreCode = S.StoreCode    
	INNER JOIN dbo.Products P WITH(NOLOCK) ON PSI.ProductCode = P.ProductCode    
	INNER JOIN dbo.CategoryMaster CM WITH(NOLOCK) ON CM.CategoryCode=P.CategoryCode
	INNER JOIN  dbo.Brand B WITH(NOLOCK) ON P.BrandCode = B.BrandCode
	INNER JOIN dbo.Region R WITH(NOLOCK) ON R.RegionCode = S.RegionCode     
	INNER JOIN  dbo.Zone Z WITH(NOLOCK) ON Z.ZoneCode=R.ZoneCode	
			
WHERE    
			    (P.Classification = 0) 
			AND (PSI.BillDate BETWEEN @FromDate AND @Todate) 
			AND (PSI.Transactionvalidity = 0) 
			AND (S.StoreCode = @SCode OR @SCode IS NULL)  
			AND (S.StoreCluster= @SCluster OR @SCluster IS NULL)  
			AND (S.StoreBrand= @SBrand OR @SBrand IS NULL)  
			AND (R.RegionCode= @RCode OR @RCode IS NULL)  
			AND (S.StoreType= @SType OR @SType IS NULL)
			AND (R.ZoneCode = @ZCode OR @ZCode IS NULL)  
			AND (PSI.TransID IN (1,2))     
GROUP BY     

			Z.ZoneName,
			S.StoreName, 
			S.AlternateStoreCode,
			R.RegionName ,
			S.StoreCode,    
			--PSI.BillDate,  
			CONVERT(VARCHAR, YEAR(PSI.BillDate)),
			DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate),
			CM.Description,
			B.BrandName)C	
			SELECT t.*,CAST((ISNULL(t.Salevalue,0)/CASE WHEN ISNULL(S.Total,0)=0 THEN 1 ELSE S.Total END) AS NUMERIC(15,2)) *100 AS [SaleGrowth%] FROM  #CategtempC t left join 
			(SELECT SUM(saleValue) AS total,[MON-Year],Syear,StoreCode FROM #CategtempC GROUP BY [MON-Year],Syear,StoreCode) S ON S.[MON-Year]=t.[MON-Year] AND S.Syear=t.Syear	 AND S.StoreCode=t.StoreCode 		  
			END
 IF  @ReportMode='Contribution' AND @ReportType='Productwise'
 BEGIN
  SELECT * INTO #ProdtempC FROM (
     SELECT     
	        Z.ZoneName AS Zone,
            R.RegionName AS Region,
			S.AlternateStoreCode AS AlternateStoreCode,
			S.StoreName AS Store,
			S.StoreCode,  
			P.ProductName As PRODLINE,
			B.BrandName AS Brand,    		    
			CONVERT(VARCHAR, YEAR(PSI.BillDate)) AS [SYear],
			DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate) AS [MON-Year],  
			SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END) AS SaleValue--,
			--CAST(SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END)/(@@TCSaleVe) AS NUMERIC(15,2)) *100  AS [SaleGrowth%]			
			FROM 
			Primary_ProductSaleInfo(@FromDate,@Todate,@ZCode,@RCode,@SCode,@SType,@SCluster,@SBrand,1,2,NULL,NULL)PSI
	INNER JOIN dbo.Store S WITH(NOLOCK) ON PSI.StoreCode = S.StoreCode    
	INNER JOIN dbo.Products P WITH(NOLOCK) ON PSI.ProductCode = P.ProductCode    	
	INNER JOIN  dbo.Brand B WITH(NOLOCK) ON P.BrandCode = B.BrandCode
	INNER JOIN dbo.Region R WITH(NOLOCK) ON R.RegionCode = S.RegionCode     
	INNER JOIN  dbo.Zone Z WITH(NOLOCK) ON Z.ZoneCode=R.ZoneCode	
			
WHERE    
			    (P.Classification = 0) 
			AND (PSI.BillDate BETWEEN @FromDate AND @Todate) 
			AND (PSI.Transactionvalidity = 0) 
			AND (S.StoreCode = @SCode OR @SCode IS NULL)  
			AND (S.StoreCluster= @SCluster OR @SCluster IS NULL)  
			AND (S.StoreBrand= @SBrand OR @SBrand IS NULL)  
			AND (R.RegionCode= @RCode OR @RCode IS NULL)  
			AND (S.StoreType= @SType OR @SType IS NULL)
			AND (R.ZoneCode = @ZCode OR @ZCode IS NULL)  
			AND (PSI.TransID IN (1,2))     
GROUP BY     

			Z.ZoneName,
			S.StoreName, 
			S.AlternateStoreCode,
			R.RegionName ,  
			S.StoreCode,  
			--PSI.BillDate,  
			CONVERT(VARCHAR, YEAR(PSI.BillDate)),
			DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate),
			P.ProductName,
			B.BrandName	) TP	
			SELECT t.*,CAST((ISNULL(t.Salevalue,0)/CASE WHEN ISNULL(S.Total,0)=0 THEN 1 ELSE S.Total END)  AS NUMERIC(15,2))*100 AS [SaleGrowth%] FROM  #ProdtempC t left join 
			(SELECT SUM(ISNULL(saleValue,0)) AS total,[MON-Year],Syear,StoreCode  FROM #ProdtempC GROUP BY [MON-Year],Syear,StoreCode) S ON S.[MON-Year]=t.[MON-Year] AND S.Syear=t.Syear	AND S.StoreCode=t.StoreCode  			  
			END

ELSE IF @ReportMode='Growth' AND @ReportType='Brandwise' 

	BEGIN

CREATE TABLE #BrandTempGM (Zone VARCHAR(100),Region VARCHAR(100),StoreCode INT,AlternateStoreCode VARCHAR(50),
                           Store VARCHAR(150),Brand VARCHAR(100),SYear VARCHAR(10),[MON-Year] VARCHAR(20),[MON] VARCHAR(2),SaleValue FLOAT)
INSERT    #BrandTempGM   

   SELECT   
            Z.ZoneName AS Zone,
            R.RegionName AS Region,
            S.StoreCode AS StoreCode,
            S.AlternateStoreCode AS AlternateStoreCode,
            S.StoreName AS Store,   
            B.BrandName AS Brand,   
            CONVERT(VARCHAR, YEAR(PSI.BillDate)) AS [SYear],
            --DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate) AS [MON-Year],
            CONVERT(VARCHAR,MONTH(PSI.BillDate),100) + '/' + CONVERT(VARCHAR,YEAR(PSI.BillDate),120) AS [MON-Year],
            CONVERT(VARCHAR,MONTH(PSI.BillDate),100) AS [MON],
            SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END) AS SaleValue
            --CAST(SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END)/(@@TBSaleVe) AS NUMERIC(15,2)) *100  AS [SaleGrowth%]           
               
    FROM   
            --[dbo].[ffn_ProductsaleInfo](@FromMonth,@ToMonth,@SCode)    PSI
            Primary_ProductSaleInfo(@FromDate,@Todate,@ZCode,@RCode,@SCode,@SType,@SCluster,@SBrand,1,2,NULL,NULL)PSI
    INNER JOIN dbo.Store S WITH(NOLOCK) ON PSI.StoreCode = S.StoreCode   
    INNER JOIN dbo.Products P WITH(NOLOCK) ON PSI.ProductCode = P.ProductCode   
    INNER JOIN  dbo.Brand B WITH(NOLOCK) ON P.BrandCode = B.BrandCode
    INNER JOIN dbo.Region R WITH(NOLOCK) ON R.RegionCode = S.RegionCode    
    INNER JOIN  dbo.Zone Z WITH(NOLOCK) ON Z.ZoneCode=R.ZoneCode
    LEFT JOIN dbo.CategoryMaster CM WITH(NOLOCK) ON CM.CategoryCode=P.categoryCode
           
WHERE   
                (P.Classification = 0)
            AND (PSI.BillDate BETWEEN @FromDate AND @Todate)
            AND (PSI.Transactionvalidity = 0)
            AND (S.StoreCode = @SCode OR @SCode IS NULL) 
            AND (S.StoreCluster= @SCluster OR @SCluster IS NULL) 
            AND (S.StoreBrand= @SBrand OR @SBrand IS NULL) 
            AND (R.RegionCode= @RCode OR @RCode IS NULL) 
            AND (S.StoreType= @SType OR @SType IS NULL)
            AND (R.ZoneCode = @ZCode OR @ZCode IS NULL) 
            AND (PSI.TransID IN (1,2))    
GROUP BY    

            Z.ZoneName,
            S.StoreName,
            S.StoreCode,
            S.AlternateStoreCode,
            R.RegionName , 
            PSI.BillDate, 
            CONVERT(VARCHAR, YEAR(PSI.BillDate)),
            DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate),
            B.BrandName

    SET @StartDate=YEAR(@FromDate);
    SET @EndDate = YEAR(@ToDate);
    SET @StartMonth = MONTH(@FromDate);
    SET @EndMonth= MONTH(@ToDate);
    SET @SQL ='ALTER TABLE #BrandTempGM ADD [SaleGrowth%] FLOAT'; 
    EXEC SP_EXECUTESQL @SQL
   
    WHILE(@StartDate<@EndDate)
    BEGIN
   
    SET @SQL= ' UPDATE #BrandTempGM SET [SaleGrowth%] =0'
            EXEC SP_EXECUTESQL @SQL
            SELECT      #BrandTempGM.Zone,
                        #BrandTempGM.Region,
                        #BrandTempGM.Store,
                        #BrandTempGM.StoreCode,
                        #BrandTempGM.SYear,
                        #BrandTempGM.Brand,
                        #BrandTempGM.[MON-Year],
                        #BrandTempGM.[Mon],
                        ISNULL(PS.SaleValue,0) AS PSaleValue,
                        ISNULL(NS.SaleValue,0) AS NSaleValue,                   
                        CAST(CASE WHEN ISNULL(PS.SaleValue,0)=0 THEN 0
                             WHEN ISNULL(PS.SaleValue,0)<>0 THEN (((ISNULL(NS.SaleValue,0)-ISNULL(PS.SaleValue,0))/CASE WHEN ISNULL(PS.SaleValue,0)=0 THEN 1 ELSE PS.SaleValue END )*100)
                             WHEN ISNULL(NS.SaleValue,0)=0 THEN 100
                              END AS NUMERIC(16,2))  AS [SaleGrowth%]              
                INTO #TempGrowthY1
                FROM
                      #BrandTempGM
                    LEFT JOIN
                        (
                            SELECT S.StoreCode,
                                   S.SaleValue
                                 
                            FROM
                                #BrandTempGM S
                            WHERE S.SYear = @StartDate  AND S.SaleValue <> 0
                        ) AS PS ON PS.StoreCode=#BrandTempGM.StoreCode
               
                        LEFT JOIN
                        (
                            SELECT S.StoreCode,
                                   S.SaleValue
                                 
                            FROM
                                #BrandTempGM S
                            WHERE S.SYear = @StartDate+1  AND S.SaleValue <> 0
                        ) AS NS ON NS.StoreCode=#BrandTempGM.StoreCode
                       
                        SET @SQL=''
                        SET @SQL= ' UPDATE TS SET TS.[SaleGrowth%] = TP.[SaleGrowth%]'
                        SET @SQL= @SQL+ ' FROM #BrandTempGM TS '   
                        SET @SQL= @SQL+ ' INNER JOIN  #TempGrowthY1 TP ON TP.StoreCode=TS.StoreCode'   
                        SET @SQL= @SQL+ ' WHERE TS.SYear='+CONVERT(NVARCHAR,@StartDate+1)+''
                        --SET @SQL= @SQL+ ' AND TS.[MON]='+CONVERT(NVARCHAR,@StartMonth+1)+''
                            EXEC SP_EXECUTESQL @SQL
                        SET @StartDate=@StartDate+1;
                        --SET @StartMonth=@StartMonth+1
                        DROP TABLE #TempGrowthY1
                         END
         SELECT * INTO #tempGrowthExactM FROM(SELECT Zone,Region,Store,AlternateStoreCode,Brand,SYear,[MON-Year],[MON],SUM(SaleValue) AS SaleValue, SUM([SaleGrowth%]) [SaleGrowth%] FROM #BrandTempGM GROUP BY syear,[MON-Year],[MON],AlternateStoreCode,Zone,Region,Store,Brand )X
         SELECT * INTO #dataM FROM (
         SELECT Zone,Region,Store,AlternateStoreCode,Brand,SYear,[MON-Year],[MON], SaleValue,
       LEAD(SaleValue, 1) OVER(PARTITION BY Store,Brand ORDER BY Store,Brand,SYear,[MON-Year] DESC) AS 'NextTransStart'   FROM #tempGrowthExactM )Y 
       SELECT  Zone,Region,Store,AlternateStoreCode,Brand,SYear,[MON-Year], SaleValue,CAST(ISNULL(((salevalue-NextTransStart)/CASE WHEN ISNULL(NextTransStart,0)=0 THEN 1 ELSE NextTransStart END)*100,0)AS NUMERIC(15,2)) AS [SaleGrowth%] from #dataM
	    --SELECT Zone,Region,Store,Brand,StoreCode,SYear,[MON-Year],SUM(SaleValue) AS SaleValue,SUM([SaleGrowth%]) [SaleGrowth%] FROM #BrandTempGY GROUP BY SYear,[MON-Year],StoreCode,Zone,Region,Store,Brand ORDER BY SYear
	     

END
			IF @ReportMode='Growth' AND @ReportType='Categorywise' 
BEGIN

CREATE TABLE #CategTempGM (Zone VARCHAR(100),Region VARCHAR(100),StoreCode INT,AlternateStoreCode VARCHAR(50),
                           Store VARCHAR(150),Brand VARCHAR(100),Category VARCHAR(100),SYear VARCHAR(10),[MON-Year] VARCHAR(20),[MON] VARCHAR(2),SaleValue FLOAT)
INSERT    #CategTempGM   

   SELECT   
            Z.ZoneName AS Zone,
            R.RegionName AS Region,
            S.StoreCode AS StoreCode,
            S.AlternateStoreCode AS AlternateStoreCode,
            S.StoreName AS Store,   
            B.BrandName AS Brand,
			CM.Description AS Category,   
            CONVERT(VARCHAR, YEAR(PSI.BillDate)) AS [SYear],
            --DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate) AS [MON-Year],
            CONVERT(VARCHAR,MONTH(PSI.BillDate),100) + '/' + CONVERT(VARCHAR,YEAR(PSI.BillDate),120) AS [MON-Year],
            CONVERT(VARCHAR,MONTH(PSI.BillDate),100) AS [MON],
            SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END) AS SaleValue
            --CAST(SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END)/(@@TBSaleVe) AS NUMERIC(15,2)) *100  AS [SaleGrowth%]           
               
    FROM   
            --[dbo].[ffn_ProductsaleInfo](@FromMonth,@ToMonth,@SCode)    PSI
            Primary_ProductSaleInfo(@FromDate,@Todate,@ZCode,@RCode,@SCode,@SType,@SCluster,@SBrand,1,2,NULL,NULL)PSI
    INNER JOIN dbo.Store S WITH(NOLOCK) ON PSI.StoreCode = S.StoreCode   
    INNER JOIN dbo.Products P WITH(NOLOCK) ON PSI.ProductCode = P.ProductCode   
    INNER JOIN  dbo.Brand B WITH(NOLOCK) ON P.BrandCode = B.BrandCode
    INNER JOIN dbo.Region R WITH(NOLOCK) ON R.RegionCode = S.RegionCode    
    INNER JOIN  dbo.Zone Z WITH(NOLOCK) ON Z.ZoneCode=R.ZoneCode
    LEFT JOIN dbo.CategoryMaster CM WITH(NOLOCK) ON CM.CategoryCode=P.categoryCode
           
WHERE   
                (P.Classification = 0)
            AND (PSI.BillDate BETWEEN @FromDate AND @Todate)
            AND (PSI.Transactionvalidity = 0)
            AND (S.StoreCode = @SCode OR @SCode IS NULL) 
            AND (S.StoreCluster= @SCluster OR @SCluster IS NULL) 
            AND (S.StoreBrand= @SBrand OR @SBrand IS NULL) 
            AND (R.RegionCode= @RCode OR @RCode IS NULL) 
            AND (S.StoreType= @SType OR @SType IS NULL)
            AND (R.ZoneCode = @ZCode OR @ZCode IS NULL) 
            AND (PSI.TransID IN (1,2))    
GROUP BY    

            Z.ZoneName,
            S.StoreName,
            S.StoreCode,
            S.AlternateStoreCode,
            R.RegionName , 
            PSI.BillDate, 
            CONVERT(VARCHAR, YEAR(PSI.BillDate)),
            DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate),
            B.BrandName,
			CM.Description

    SET @StartDate=YEAR(@FromDate);
    SET @EndDate = YEAR(@ToDate);
    SET @StartMonth = MONTH(@FromDate);
    SET @EndMonth= MONTH(@ToDate);
    SET @SQL ='ALTER TABLE #CategTempGM ADD [SaleGrowth%] FLOAT'; 
    EXEC SP_EXECUTESQL @SQL
   
    WHILE(@StartDate=@EndDate AND @StartMonth<@EndMonth)
    BEGIN
   
    SET @SQL= ' UPDATE #CategTempGM SET [SaleGrowth%] =0'
            EXEC SP_EXECUTESQL @SQL
            SELECT      #CategTempGM.Zone,
                        #CategTempGM.Region,
                        #CategTempGM.Store,
                        #CategTempGM.StoreCode,
                        #CategTempGM.SYear,
                        #CategTempGM.Brand,
						#CategTempGM.Category,
                        #CategTempGM.[MON-Year],
                        #CategTempGM.[Mon],
                        ISNULL(PS.SaleValue,0) AS PSaleValue,
                        ISNULL(NS.SaleValue,0) AS NSaleValue,                   
                        CAST(CASE WHEN ISNULL(PS.SaleValue,0)=0 THEN 0
                             WHEN ISNULL(PS.SaleValue,0)<>0 THEN (((ISNULL(NS.SaleValue,0)-ISNULL(PS.SaleValue,0))/CASE WHEN ISNULL(PS.SaleValue,0)=0 THEN 1 ELSE PS.SaleValue END )*100)
                             WHEN ISNULL(NS.SaleValue,0)=0 THEN 100
                              END AS NUMERIC(16,2))  AS [SaleGrowth%]              
                INTO #TempGrowthM1
                FROM
                      #CategTempGM
                    LEFT JOIN
                        (
                            SELECT S.StoreCode,
                                   S.SaleValue
                                 
                            FROM
                                #CategTempGM S
                            WHERE S.SYear = @StartDate AND S.[MON]=@StartMonth AND S.SaleValue <> 0
                        ) AS PS ON PS.StoreCode=#CategTempGM.StoreCode
               
                        LEFT JOIN
                        (
                            SELECT S.StoreCode,
                                   S.SaleValue
                                 
                            FROM
                                #CategTempGM S
                            WHERE S.SYear = @StartDate AND S.[MON]=@StartMonth+1 AND S.SaleValue <> 0
                        ) AS NS ON NS.StoreCode=#CategTempGM.StoreCode
                       
                        SET @SQL=''
                        SET @SQL= ' UPDATE TS SET TS.[SaleGrowth%] = TP.[SaleGrowth%]'
                        SET @SQL= @SQL+ ' FROM #CategTempGM TS '   
                        SET @SQL= @SQL+ ' INNER JOIN  #TempGrowthM1 TP ON TP.StoreCode=TS.StoreCode'   
                        SET @SQL= @SQL+ ' WHERE TS.SYear='+CONVERT(NVARCHAR,@StartDate)+''
                        SET @SQL= @SQL+ ' AND TS.[MON]='+CONVERT(NVARCHAR,@StartMonth+1)+''
                            EXEC SP_EXECUTESQL @SQL

                        SET @StartDate=@StartDate;
                        SET @StartMonth=@StartMonth+1
                        DROP TABLE #TempGrowthM1
                         END
         SELECT * INTO #tempGrowthExactCM FROM(SELECT Zone,Region,Store,AlternateStoreCode,Brand,Category,SYear,[MON-Year],[MON],SUM(SaleValue) AS SaleValue, SUM([SaleGrowth%]) [SaleGrowth%] FROM #CategTempGM GROUP BY syear,[MON-Year],[MON],AlternateStoreCode,Zone,Region,Store,Brand,Category)X
         SELECT * INTO #dataCM FROM (
         SELECT Zone,Region,Store,AlternateStoreCode,Brand,Category,SYear,[MON-Year],[MON], SaleValue,
       LEAD(SaleValue, 1) OVER(PARTITION BY Store,Category ORDER BY Store,Category,SYear,[MON-Year] DESC) AS 'NextTransStart'   FROM #tempGrowthExactCM )Y 
       SELECT  Zone,Region,Store,AlternateStoreCode,Brand,Category,SYear,[MON-Year], SaleValue,CAST(ISNULL(((salevalue-NextTransStart)/CASE WHEN ISNULL(NextTransStart,0)=0 THEN 1 ELSE NextTransStart END)*100,0)AS NUMERIC(15,2)) AS [SaleGrowth%] from #dataCM
	   DROP TABLE #CategTempGM

END

IF  @ReportMode='Growth' AND @ReportType='Productwise'
 BEGIN
  
CREATE TABLE #ProdTempGM (Zone VARCHAR(100),Region VARCHAR(100),StoreCode INT,AlternateStoreCode VARCHAR(50),
                           Store VARCHAR(150),Brand VARCHAR(100),PRODLINE VARCHAR(100),SYear VARCHAR(10),[MON-Year] VARCHAR(20),[MON] VARCHAR(2),SaleValue FLOAT)
INSERT    #ProdTempGM   

   SELECT   
            Z.ZoneName AS Zone,
            R.RegionName AS Region,
            S.StoreCode AS StoreCode,
            S.AlternateStoreCode AS AlternateStoreCode,
            S.StoreName AS Store,   
            B.BrandName AS Brand,
			P.productName As PRODLINE,
			--CM.Description AS Category,   
            CONVERT(VARCHAR, YEAR(PSI.BillDate)) AS [SYear],
            --DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate) AS [MON-Year],
            CONVERT(VARCHAR,MONTH(PSI.BillDate),100) + '/' + CONVERT(VARCHAR,YEAR(PSI.BillDate),120) AS [MON-Year],
            CONVERT(VARCHAR,MONTH(PSI.BillDate),100) AS [MON],
            SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END) AS SaleValue
            --CAST(SUM(CASE WHEN PSI.TransID=1  THEN (ISNULL(PSI.TransactionAmount,0)) WHEN PSI.TransID=2  THEN ABS(ISNULL(PSI.TransactionAmount,0))*-1 END)/(@@TBSaleVe) AS NUMERIC(15,2)) *100  AS [SaleGrowth%]           
               
    FROM   
            --[dbo].[ffn_ProductsaleInfo](@FromMonth,@ToMonth,@SCode)    PSI
            Primary_ProductSaleInfo(@FromDate,@Todate,@ZCode,@RCode,@SCode,@SType,@SCluster,@SBrand,1,2,NULL,NULL)PSI
    INNER JOIN dbo.Store S WITH(NOLOCK) ON PSI.StoreCode = S.StoreCode   
    INNER JOIN dbo.Products P WITH(NOLOCK) ON PSI.ProductCode = P.ProductCode   
    INNER JOIN  dbo.Brand B WITH(NOLOCK) ON P.BrandCode = B.BrandCode
    INNER JOIN dbo.Region R WITH(NOLOCK) ON R.RegionCode = S.RegionCode    
    INNER JOIN  dbo.Zone Z WITH(NOLOCK) ON Z.ZoneCode=R.ZoneCode
    LEFT JOIN dbo.CategoryMaster CM WITH(NOLOCK) ON CM.CategoryCode=P.categoryCode
           
WHERE   
                (P.Classification = 0)
            AND (PSI.BillDate BETWEEN @FromDate AND @Todate)
            AND (PSI.Transactionvalidity = 0)
            AND (S.StoreCode = @SCode OR @SCode IS NULL) 
            AND (S.StoreCluster= @SCluster OR @SCluster IS NULL) 
            AND (S.StoreBrand= @SBrand OR @SBrand IS NULL) 
            AND (R.RegionCode= @RCode OR @RCode IS NULL) 
            AND (S.StoreType= @SType OR @SType IS NULL)
            AND (R.ZoneCode = @ZCode OR @ZCode IS NULL) 
            AND (PSI.TransID IN (1,2))    
GROUP BY    

            Z.ZoneName,
            S.StoreName,
            S.StoreCode,
            S.AlternateStoreCode,
            R.RegionName , 
            PSI.BillDate, 
            CONVERT(VARCHAR, YEAR(PSI.BillDate)),
            DATENAME(MONTH,PSI.BillDate) + ' ' + DATENAME(YEAR, PSI.BillDate),
            B.BrandName,
			--CM.Description
			P.ProductName

    SET @StartDate=YEAR(@FromDate);
    SET @EndDate = YEAR(@ToDate);
    SET @StartMonth = MONTH(@FromDate);
    SET @EndMonth= MONTH(@ToDate);
    SET @SQL ='ALTER TABLE #ProdTempGM ADD [SaleGrowth%] FLOAT'; 
    EXEC SP_EXECUTESQL @SQL
   
    WHILE(@StartDate=@EndDate AND @StartMonth<@EndMonth)
    BEGIN
   
    SET @SQL= ' UPDATE #ProdTempGM SET [SaleGrowth%] =0'
            EXEC SP_EXECUTESQL @SQL
            SELECT      #ProdTempGM.Zone,
                        #ProdTempGM.Region,
                        #ProdTempGM.Store,
                        #ProdTempGM.StoreCode,
                        #ProdTempGM.SYear,
                        #ProdTempGM.Brand,
						--#ProdTempGM.Category,
						#ProdTempGM.PRODLINE,
                        #ProdTempGM.[MON-Year],
                        #ProdTempGM.[Mon],
                        ISNULL(PS.SaleValue,0) AS PSaleValue,
                        ISNULL(NS.SaleValue,0) AS NSaleValue,                   
                        CAST(CASE WHEN ISNULL(PS.SaleValue,0)=0 THEN 0
                             WHEN ISNULL(PS.SaleValue,0)<>0 THEN (((ISNULL(NS.SaleValue,0)-ISNULL(PS.SaleValue,0))/CASE WHEN ISNULL(PS.SaleValue,0)=0 THEN 1 ELSE PS.SaleValue END )*100)
                             WHEN ISNULL(NS.SaleValue,0)=0 THEN 100
                              END AS NUMERIC(16,2))  AS [SaleGrowth%]              
                INTO #TempGrowthM2
                FROM
                      #ProdTempGM
                    LEFT JOIN
                        (
                            SELECT S.StoreCode,
                                   S.SaleValue
                                 
                            FROM
                                #ProdTempGM S
                            WHERE S.SYear = @StartDate AND S.[MON]=@StartMonth AND S.SaleValue <> 0
                        ) AS PS ON PS.StoreCode=#ProdTempGM.StoreCode
               
                        LEFT JOIN
                        (
                            SELECT S.StoreCode,
                                   S.SaleValue
                                 
                            FROM
                                #ProdTempGM S
                            WHERE S.SYear = @StartDate AND S.[MON]=@StartMonth+1 AND S.SaleValue <> 0
                        ) AS NS ON NS.StoreCode=#ProdTempGM.StoreCode
                       
                        SET @SQL=''
                        SET @SQL= ' UPDATE TS SET TS.[SaleGrowth%] = TP.[SaleGrowth%]'
                        SET @SQL= @SQL+ ' FROM #ProdTempGM TS '   
                        SET @SQL= @SQL+ ' INNER JOIN  #TempGrowthM2 TP ON TP.StoreCode=TS.StoreCode'   
                        SET @SQL= @SQL+ ' WHERE TS.SYear='+CONVERT(NVARCHAR,@StartDate)+''
                        SET @SQL= @SQL+ ' AND TS.[MON]='+CONVERT(NVARCHAR,@StartMonth+1)+''
                            EXEC SP_EXECUTESQL @SQL

                        SET @StartDate=@StartDate;
                        SET @StartMonth=@StartMonth+1
                        DROP TABLE #TempGrowthM2
                         END
         SELECT * INTO #tempGrowthExactPM FROM(SELECT Zone,Region,Store,AlternateStoreCode,Brand,PRODLINE,SYear,[MON-Year],[MON],SUM(SaleValue) AS SaleValue, SUM([SaleGrowth%]) [SaleGrowth%] FROM #ProdTempGM GROUP BY syear,[MON-Year],[MON],AlternateStoreCode,Zone,Region,Store,Brand,PRODLINE)X
         SELECT * INTO #dataPM FROM (
         SELECT Zone,Region,Store,AlternateStoreCode,Brand,PRODLINE,SYear,[MON-Year],[MON], SaleValue,
       LEAD(SaleValue, 1) OVER(PARTITION BY Store,PRODLINE ORDER BY Store,PRODLINE,SYear,[MON-Year] DESC) AS 'NextTransStart'   FROM #tempGrowthExactPM )Y 
       SELECT  Zone,Region,Store,AlternateStoreCode,Brand,PRODLINE,SYear,[MON-Year], SaleValue,CAST(ISNULL(((salevalue-NextTransStart)/CASE WHEN ISNULL(NextTransStart,0)=0 THEN 1 ELSE NextTransStart END)*100,0)AS NUMERIC(15,2)) AS [SaleGrowth%] from #dataPM
	     DROP TABLE #ProdTempGM
			END
	  END

	 
   END