SQL server 同环比计算模板

1、计算 月 年 季度的环比和同比 

计算公式如下：

环比增长率 = （本期数 - 上期数） / |上期数| × 100%
同比增长率 = （本期数 - 同期数） / |同期数| * 100%

--- dbo.ads_erp_finance_gross_profit_actual_invoice_yoy_mom_month
    -- Insert statements for procedure here
	DELETE FROM dbo.ads_erp_finance_gross_profit_actual_invoice_yoy_mom_month WHERE dt = @DateThreshold
    BEGIN TRY  

		DECLARE @Sql44 NVARCHAR(MAX) =  N'WITH month_sales AS ( select dt,year,CAST(SUBSTRING(ym, 6, 2) AS INT) AS month, money as month_money from dws_erp_finance_gross_profit_actual_invoice_month where dt = ' + QUOTENAME(CONVERT(NVARCHAR(10), @DateThreshold, 120), '''') + N'
		), sales AS (  SELECT t3.year,t3.month,t3.month_money AS month_money,t3.same_month_last_year_money,
			  ROW_NUMBER() OVER (PARTITION BY [year], [month] ORDER BY [month_money]) AS rn 
			  FROM  (SELECT t1.year,t2.year as last_year,t1.month,t1.month_money,t2.month_money AS same_month_last_year_money FROM month_sales AS t1 LEFT JOIN month_sales AS t2 ON t1.month = t2.month AND t1.year - 1 = t2.year) t3
				), months AS ( SELECT DISTINCT [year], month FROM sales )  
		INSERT INTO dbo.ads_erp_finance_gross_profit_actual_invoice_yoy_mom_month ([dt],[ym],[month_money],[previous_month_money],[same_month_last_year_money],[yoy_growth_rate],[mom_growth_rate])
		select ' + QUOTENAME(CONVERT(NVARCHAR(10), @DateThreshold, 120), '''') + N' as dt, 
		CAST(year AS VARCHAR)+ ''-''+  RIGHT(''0'' + CAST(month AS VARCHAR), 2) AS ym,
			month_money,previous_month_money,same_month_last_year_money,
			CASE WHEN same_month_last_year_money = 0 THEN null ELSE ((month_money - same_month_last_year_money) / ABS(same_month_last_year_money)) * 100 END AS yoy_growth_rate,
			CASE WHEN previous_month_money = 0 THEN null ELSE ((month_money - previous_month_money) / ABS(previous_month_money)) * 100 END AS mom_growth_rate
		from ( SELECT DISTINCT q.[year],q.[month],case when s.[month_money] is NULL then 0 else s.[month_money] end AS month_money,    
				case when sqs.[month_money] is NULL then 0 else sqs.[month_money] end AS same_month_last_year_money,t4.previous_month_money FROM months q  
			JOIN sales s ON q.[year] = s.[year] AND q.[month] = s.[month] AND s.rn = 1  
			LEFT JOIN sales sqs ON q.[year] - 1 = sqs.[year] AND q.[month] = sqs.[month] AND sqs.rn = 1
			left join ( select t2.dt,ym,t2.year,t2.month,t2.month_money,case when monthdiff = 1 then previous_month_money else 0 end  previous_month_money
				from(select dt,ym,SUBSTRING(ym, 1, 4) AS year,CAST(SUBSTRING(ym, 6, 2) AS INT) AS month,previous_month,month_money,previous_month_money,
						DATEDIFF(MONTH, CAST(CAST(previous_month AS VARCHAR) + ''-01'' AS DATE) , CAST(CAST(ym AS VARCHAR) + ''-01'' AS DATE)) monthdiff
					from(SELECT t1.dt,t1.year,t1.quarter, t1.ym, CASE WHEN t1_prev.ym IS NULL THEN ''1970-01'' ELSE t1_prev.ym END AS previous_month,
							CASE WHEN t1_prev.money IS NULL THEN 0 ELSE t1_prev.money END AS previous_month_money,t1.money AS month_money FROM dws_erp_finance_gross_profit_actual_invoice_month t1
						LEFT JOIN dws_erp_finance_gross_profit_actual_invoice_month t1_prev ON t1.dt = t1_prev.dt AND t1.ym > t1_prev.ym
						WHERE t1.dt = ' + QUOTENAME(CONVERT(NVARCHAR(10), @DateThreshold, 120), '''') + N' AND NOT EXISTS ( SELECT 1 FROM dws_erp_finance_gross_profit_actual_invoice_month t2 WHERE t2.dt = t1.dt AND t2.ym > t1_prev.ym AND t2.ym < t1.ym )
             ) t1) t2 ) t4 on q.year = t4.year and q.month = t4.month) t1'
		
		SET @Sql44 = REPLACE(@Sql44, '@DateThreshold', @DateThreshold)
	    print(@Sql44)
	    EXEC sp_executesql @Sql44

    END TRY  
      

    BEGIN CATCH  

        DECLARE @ErrorMessage44 NVARCHAR(4000)  
        SET @ErrorMessage44 = ERROR_MESSAGE()  
          
        RAISERROR (@ErrorMessage44, 16, 1)  
          


    END CATCH


	--- dbo.ads_erp_finance_gross_profit_actual_invoice_yoy_pop_quarter
    -- Insert statements for procedure here
	DELETE FROM dbo.ads_erp_finance_gross_profit_actual_invoice_yoy_pop_quarter WHERE dt = @DateThreshold
    BEGIN TRY  


	    DECLARE @Sql45 NVARCHAR(MAX) =  N'WITH year_quarter_sales AS (SELECT [year] + '' Q''+ [quarter] AS year_quarter,SUM(money) AS quarter_money
				FROM dws_erp_finance_gross_profit_actual_invoice_month WHERE dt = ' + QUOTENAME(CONVERT(NVARCHAR(10), @DateThreshold, 120), '''') + N' GROUP BY [year] + '' Q''+ [quarter]
		   ),quarter_sales AS (SELECT [year],[quarter],sum(money) as quarter_money FROM dws_erp_finance_gross_profit_actual_invoice_month where dt = ' + QUOTENAME(CONVERT(NVARCHAR(10), @DateThreshold, 120), '''') + N' group by year, quarter
		  ),sales AS ( SELECT  t3.year, t3.quarter,t3.quarter_money AS [quarter_money], t3.same_quarter_last_year_money, ROW_NUMBER() OVER (PARTITION BY [year], [quarter] ORDER BY [quarter_money]) AS rn  
				FROM  (SELECT t1.year,t2.year as last_year,t1.quarter,t1.quarter_money,t2.quarter_money AS same_quarter_last_year_money FROM quarter_sales as t1 
						LEFT JOIN quarter_sales as t2 ON t1.quarter = t2.quarter AND t1.year - 1 = t2.year) t3
		 ), quarters AS ( SELECT DISTINCT [year], [quarter] FROM sales )
		INSERT INTO dbo.ads_erp_finance_gross_profit_actual_invoice_yoy_pop_quarter ([dt],[year],[quarter],[quarter_money],[previous_quarter_money],[same_quarter_last_year_money],[yoy_growth_rate],[qoq_growth_rate])
		select ' + QUOTENAME(CONVERT(NVARCHAR(10), @DateThreshold, 120), '''') + N' as dt,year,quarter,quarter_money,previous_quarter_money,same_quarter_last_year_money,
			CASE WHEN same_quarter_last_year_money = 0 THEN null ELSE ((quarter_money - same_quarter_last_year_money) / ABS(same_quarter_last_year_money)) * 100 END AS yoy_growth_rate,
			CASE WHEN previous_quarter_money = 0 THEN null ELSE ((quarter_money - previous_quarter_money) / ABS(previous_quarter_money)) * 100 END AS qoq_growth_rate
		from ( SELECT DISTINCT q.[year],q.[quarter],case when s.[quarter_money] is NULL then 0 else s.[quarter_money] end AS quarter_money,   
				case when pqs.[previous_quarter_money] is NULL then 0 else pqs.[previous_quarter_money] end AS previous_quarter_money,   
				case when sqs.[quarter_money] is NULL then 0 else sqs.[quarter_money] end AS same_quarter_last_year_money FROM quarters q  
			JOIN sales s ON q.[year] = s.[year] AND q.[quarter] = s.[quarter] AND s.rn = 1   
			LEFT JOIN sales sqs ON q.[year] - 1 = sqs.[year] AND q.[quarter] = sqs.[quarter] AND sqs.rn = 1
			LEFT JOIN (select t3.dt,t3.year,SUBSTRING(t3.year_quarter, 7, 1) AS quarter,previous_quarter,quarter_money,previous_quarter_money
				from (SELECT ' + QUOTENAME(CONVERT(NVARCHAR(10), @DateThreshold, 120), '''') + N' as dt,SUBSTRING(t1.year_quarter, 1, 4) AS year,t1.year_quarter,
						CASE WHEN t1_prev.year_quarter IS NULL THEN ''1970 Q1'' ELSE t1_prev.year_quarter END AS previous_quarter,t1.quarter_money AS quarter_money,
						CASE WHEN t1_prev.quarter_money IS NULL THEN 0 ELSE t1_prev.quarter_money END AS previous_quarter_money FROM year_quarter_sales as t1
					LEFT JOIN year_quarter_sales as t1_prev ON t1.year_quarter > t1_prev.year_quarter
					WHERE  NOT EXISTS (SELECT 1 FROM year_quarter_sales as t2 WHERE t2.year_quarter > t1_prev.year_quarter AND t2.year_quarter < t1.year_quarter)
				) t3 ) pqs ON q.[year] = pqs.[year] AND q.[quarter] = pqs.[quarter] ) t1 order by year, quarter asc' 

		SET @Sql45 = REPLACE(@Sql45, '@DateThreshold', @DateThreshold)
	    print(@Sql45)
	    EXEC sp_executesql @Sql45

    END TRY  
      

    BEGIN CATCH  

        DECLARE @ErrorMessage45 NVARCHAR(4000)  
        SET @ErrorMessage45 = ERROR_MESSAGE()  
          
        RAISERROR (@ErrorMessage45, 16, 1)  
          


    END CATCH



	--- dbo.ads_erp_finance_gross_profit_actual_invoice_yoy_year
    -- Insert statements for procedure here

    BEGIN TRY  

		DELETE FROM dbo.ads_erp_finance_gross_profit_actual_invoice_yoy_year WHERE dt = @DateThreshold

		DECLARE @Sql46 NVARCHAR(MAX) =  N'WITH year_sales AS (select year,sum(money) as year_money from dws_erp_finance_gross_profit_actual_invoice_month where dt =' + QUOTENAME(CONVERT(NVARCHAR(10), @DateThreshold, 120), '''') + N' group by year)
        INSERT INTO dbo.ads_erp_finance_gross_profit_actual_invoice_yoy_year ([dt],[year],[year_money],[previous_year_money],[yoy_growth_rate])
		select dt,year,year_money,previous_year_money,
			CASE WHEN previous_year_money = 0 THEN null ELSE ((year_money - previous_year_money) / ABS(previous_year_money)) * 100 END AS yoy_growth_rate
		from (SELECT ' + QUOTENAME(CONVERT(NVARCHAR(10), @DateThreshold, 120), '''') + N' as dt,t1.year,
			 CASE WHEN t1_prev.year IS NULL THEN ''1970'' ELSE t1_prev.year END AS previous_year,t1.year_money AS year_money,
			CASE WHEN t1_prev.year_money IS NULL THEN 0 ELSE t1_prev.year_money END AS previous_year_money FROM  year_sales as t1
			LEFT JOIN year_sales as t1_prev ON t1.year > t1_prev.year
			WHERE  NOT EXISTS ( SELECT 1 FROM year_sales as t2 WHERE t2.year > t1_prev.year AND t2.year < t1.year)) t2'
 
        SET @Sql46 = REPLACE(@Sql46, '@DateThreshold', @DateThreshold)
	    print(@Sql46)
	    EXEC sp_executesql @Sql46	

    END TRY  
      

    BEGIN CATCH  

        DECLARE @ErrorMessage46 NVARCHAR(4000)  
        SET @ErrorMessage46 = ERROR_MESSAGE()  
          
        RAISERROR (@ErrorMessage46, 16, 1)  
          


    END CATCH

2、基础月表：

--- dbo.dws_erp_finance_gross_profit_actual_invoice_month
    -- Insert statements for procedure here

    BEGIN TRY  

		DELETE FROM dbo.dws_erp_finance_gross_profit_actual_invoice_month WHERE dt = @DateThreshold  

        INSERT INTO dbo.dws_erp_finance_gross_profit_actual_invoice_month ([dt],[year],[quarter],[ym],actual_invoice_money,sale_cost_money, money)
		select @DateThreshold as dt, [year],[quarter],[ym], actual_invoice_money,sale_cost_money,
		        actual_invoice_money - sale_cost_money  as money
		from (select t1.year,
			   t1.quarter,
			   t1.ym,
			   case when t2.actual_invoice_money is NULL then 0 else t2.actual_invoice_money end as actual_invoice_money,
			   case when t3.sale_cost_money is NULL then 0 else t3.sale_cost_money end as sale_cost_money
		from (
			select dt,year,quarter,ym
			  from dim_year_quarter_month where dt = @DateThreshold
		) t1
		left join (
		    select year,quarter,ym, money as actual_invoice_money from dws_erp_finance_paybackinvoicesure_month where dt = @DateThreshold
		) t2
		on t1.ym = t2.ym
		left join (
		   select year,quarter,ym,money as sale_cost_money from dws_erp_finance_cost_month where dt = @DateThreshold
		) t3
		on t1.ym = t3.ym) t5

		DELETE FROM dbo.dws_erp_finance_gross_profit_actual_invoice_month WHERE dt = DATEADD(day, -7, @DateThreshold)

		print('dws_erp_finance_gross_profit_actual_invoice_month')
    END TRY  
      

    BEGIN CATCH  

        DECLARE @ErrorMessage37 NVARCHAR(4000)  
        SET @ErrorMessage37 = ERROR_MESSAGE()  
          
        RAISERROR (@ErrorMessage37, 16, 1)  

    END CATCH