Business Performance Dashboard SQL

IF OBJECT_ID('tempdb..#total') IS NOT NULL BEGIN DROP TABLE #total END;

WITH rate AS (
SELECT 
	fsc.*
	,t.TIME_MONTH AS ReportEndMonth
	,t.TIME_YEAR +6 AS ReportEndYear
	,sc.SALES_CLASS_DESC
	,l.LOCATION_NAME
	,mt.MANUFACTURER
	,mt.MACHINE_MODEL
	,fsc.ACTUAL_LABOR_COST/ACTUAL_UNITS AS ActualLaborRate
	,fsc.ACTUAL_MACHINE_COST/ACTUAL_UNITS AS ActualMachineRate
	,fsc.ACTUAL_MATERIAL_COST/ACTUAL_UNITS AS ActualMaterialRate
	,fsc.ACTUAL_OVERHEAD_COST/ACTUAL_UNITS AS ActualOverheadRate
	,fsc.BUDGET_LABOR_COST/BUDGET_UNITS AS BudgetLaborRate
	,fsc.BUDGET_MACHINE_COST/BUDGET_UNITS AS BudgetMachineRate
	,fsc.BUDGET_MATERIAL_COST/BUDGET_UNITS AS BudgetMaterialRate
	,fsc.BUDGET_OVERHEAD_COST/BUDGET_UNITS AS BudgetOverheadRate
FROM W_FINANCIAL_SUMMARY_COST_F fsc
	Join W_TIME_D t ON fsc.REPORT_END_DATE_ID = t.TIME_ID
	Join W_SALES_CLASS_D sc ON sc.SALES_CLASS_ID = fsc.SALES_CLASS_ID
	Join W_LOCATION_D l ON l.LOCATION_ID = fsc.LOCATION_ID
	Join W_MACHINE_TYPE_D mt ON mt.MACHINE_TYPE_ID = fsc.MACHINE_TYPE_ID
WHERE
	fsc.ACTUAL_UNITS <> 0)

SELECT 
	*
	,ActualLaborRate+ActualMachineRate+ActualMaterialRate+ActualOverheadRate AS TotalActualRate
	,BudgetLaborRate+BudgetMachineRate+BudgetMaterialRate+BudgetOverheadRate AS TotalBudgetRate
	,BudgetLaborRate * ACTUAL_UNITS AS BudgetCostLabor
	,BudgetMachineRate * ACTUAL_UNITS AS BudgetCostMachine
	,BudgetMaterialRate * ACTUAL_UNITS AS BudgetCostMaterial
	,BudgetOverheadRate * ACTUAL_UNITS AS BudgetCostOverhead
INTO #total
FROM rate;

WITH budget AS (
SELECT
	FINANCIAL_SUMMARY_COST_ID
	,reportEndMonth
	,ReportEndYear
	,SALES_CLASS_DESC
	,LOCATION_NAME
	,MANUFACTURER
	,MACHINE_MODEL
	,Cost_Type
	,Cost
	,CASE
		WHEN Cost_Type LIKE '%Labor%' THEN 'Labor'
		WHEN Cost_Type LIKE '%Machine%' THEN 'Machine'
		WHEN Cost_Type LIKE '%Material%' THEN 'Material'
		WHEN Cost_Type LIKE '%Overhead%' THEN 'Overhead'
	End As BaseCost
FROM 
	(SELECT Cast(FINANCIAL_SUMMARY_COST_ID As NVARCHAR(MAX)) As FINANCIAL_SUMMARY_COST_ID
			,CAST(ReportEndMonth As NVARCHAR(MAX)) As ReportEndMonth
			,CAST(ReportEndYear As NVARCHAR(MAX)) As ReportEndYear
			,CAST(SALES_CLASS_DESC As NVARCHAR(MAX)) As SALES_CLASS_DESC
			,CAST(LOCATION_NAME As NVARCHAR(MAX)) As LOCATION_NAME
			,CAST(MANUFACTURER As NVARCHAR(MAX)) As MANUFACTURER
			,CAST(MACHINE_MODEL As NVARCHAR(MAX)) As MACHINE_MODEL
			,Cast(BudgetCostLabor As NVARCHAR(MAX)) As BudgetCostLabor
			,Cast(BudgetCostMachine As NVARCHAR(MAX)) As BudgetCostMachine
			,Cast(BudgetCostMaterial As NVARCHAR(MAX)) As BudgetCostMaterial
			,Cast(BudgetCostOverhead As NVARCHAR(MAX)) As BudgetCostOverhead
		FROM #total) p
	UNPIVOT
		(Cost FOR Cost_Type IN
			(BudgetCostLabor
			,BudgetCostMachine
			,BudgetCostMaterial
			,BudgetCostOverhead)
			)As upvt),

actual As(
SELECT
	FINANCIAL_SUMMARY_COST_ID
	,reportEndMonth
	,ReportEndYear
	,SALES_CLASS_DESC
	,LOCATION_NAME
	,MANUFACTURER
	,MACHINE_MODEL
	,Cost_Type
	,Cost
	,CASE
		WHEN Cost_Type LIKE '%Labor%' THEN 'Labor'
		WHEN Cost_Type LIKE '%Machine%' THEN 'Machine'
		WHEN Cost_Type LIKE '%Material%' THEN 'Material'
		WHEN Cost_Type LIKE '%Overhead%' THEN 'Overhead'
	End As BaseCost
FROM 
	(SELECT Cast(FINANCIAL_SUMMARY_COST_ID As NVARCHAR(MAX)) As FINANCIAL_SUMMARY_COST_ID
			,CAST(ReportEndMonth As NVARCHAR(MAX)) As ReportEndMonth
			,CAST(ReportEndYear As NVARCHAR(MAX)) As ReportEndYear
			,CAST(SALES_CLASS_DESC As NVARCHAR(MAX)) As SALES_CLASS_DESC
			,CAST(LOCATION_NAME As NVARCHAR(MAX)) As LOCATION_NAME
			,CAST(MANUFACTURER As NVARCHAR(MAX)) As MANUFACTURER
			,CAST(MACHINE_MODEL As NVARCHAR(MAX)) As MACHINE_MODEL
			,Cast(ACTUAL_LABOR_COST As NVARCHAR(MAX)) As ACTUAL_LABOR_COST
			,Cast(ACTUAL_MACHINE_COST As NVARCHAR(MAX)) As ACTUAL_MACHINE_COST
			,Cast(ACTUAL_MATERIAL_COST As NVARCHAR(MAX)) As ACTUAL_MATERIAL_COST
			,Cast(ACTUAL_OVERHEAD_COST As NVARCHAR(MAX)) As ACTUAL_OVERHEAD_COST
		FROM #total) p
	UNPIVOT
		(Cost FOR Cost_Type IN
			(ACTUAL_LABOR_COST
			,ACTUAL_MACHINE_COST
			,ACTUAL_MATERIAL_COST
			,ACTUAL_OVERHEAD_COST)
			)As upvt)

SELECT
	a.FINANCIAL_SUMMARY_COST_ID
	,a.Cost_Type as ActualCostType
	,a.Cost As ActualCost
	,b.Cost_Type As BudgetCostType
	,b.Cost As BudgetCost
	,a.BaseCost
	,a.ReportEndMonth
	,a.ReportEndYear
	,a.SALES_CLASS_DESC As 'Sales Class'
	,a.LOCATION_NAME As 'Location'
	,a.MANUFACTURER As Manufacturer
	,a.MACHINE_MODEL As 'Machine Model'
FROM actual a
	Join budget b ON a.FINANCIAL_SUMMARY_COST_ID = b.FINANCIAL_SUMMARY_COST_ID And a.BaseCost = b.BaseCost