SQL server 同环比计算模板
1、计算 月 年 季度的环比和同比
计算公式如下：
环比增长率 = （本期数 - 上期数） / |上期数| × 100%
同比增长率 = （本期数 - 同期数） / |同期数| * 100%
--- dbo.ads_erp_finance_gross_profit_actual_invoice_yoy_mom_month-- Insert statements for procedure hereDELETE FROM dbo.ads_erp_finance_gross_profit_actual_invoice_yoy_mom_month WHERE dt = @DateThresholdBEGIN 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_ratefrom ( 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 = 1left 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_moneyfrom(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)) monthdifffrom(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 t1LEFT JOIN dws_erp_finance_gross_profit_actual_invoice_month t1_prev ON t1.dt = t1_prev.dt AND t1.ym > t1_prev.ymWHERE 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 @Sql44END 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 hereDELETE FROM dbo.ads_erp_finance_gross_profit_actual_invoice_yoy_pop_quarter WHERE dt = @DateThresholdBEGIN TRY DECLARE @Sql45 NVARCHAR(MAX) = N'WITH year_quarter_sales AS (SELECT [year] + '' Q''+ [quarter] AS year_quarter,SUM(money) AS quarter_moneyFROM 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_ratefrom ( 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 = 1LEFT JOIN (select t3.dt,t3.year,SUBSTRING(t3.year_quarter, 7, 1) AS quarter,previous_quarter,quarter_money,previous_quarter_moneyfrom (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 t1LEFT JOIN year_quarter_sales as t1_prev ON t1.year_quarter > t1_prev.year_quarterWHERE 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 @Sql45END 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 hereBEGIN TRY DELETE FROM dbo.ads_erp_finance_gross_profit_actual_invoice_yoy_year WHERE dt = @DateThresholdDECLARE @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_ratefrom (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 t1LEFT JOIN year_sales as t1_prev ON t1.year > t1_prev.yearWHERE 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 CATCH2、基础月表：
--- dbo.dws_erp_finance_gross_profit_actual_invoice_month-- Insert statements for procedure hereBEGIN 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 moneyfrom (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_moneyfrom (select dt,year,quarter,ymfrom dim_year_quarter_month where dt = @DateThreshold) t1left join (select year,quarter,ym, money as actual_invoice_money from dws_erp_finance_paybackinvoicesure_month where dt = @DateThreshold) t2on t1.ym = t2.ymleft join (select year,quarter,ym,money as sale_cost_money from dws_erp_finance_cost_month where dt = @DateThreshold) t3on t1.ym = t3.ym) t5DELETE 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