承德做网站的公司,学校网站首页设计图片,文化网站建设心得,长沙生活信息网1、计算 月 年 季度的环比和同比
计算公式如下#xff1a;
环比增长率 #xff08;本期数 - 上期数#xff09; / |上期数| 100% 同比增长率 #xff08;本期数 - 同期数#xff09; / |同期数| * 100%
--- dbo.ads_erp_finance_gross_profit_actual_invoice_yoy_m…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) NWITH 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) t1SET 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) NWITH 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) NWITH 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)) t2SET 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 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
