21 lines
816 B
Transact-SQL
21 lines
816 B
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
ALTER VIEW [dbo].[VW_ScenarioPerformance] AS
|
|
SELECT A.ForecastScenarioId, A.ActualsScenarioId, A.ForecastScenarioType,
|
|
B.TotalCost AS ForecastTotalCost, C.TotalCost AS ActualsTotalCost,
|
|
B.TotalQuantity AS ForecastTotalQuantity, C.TotalQuantity AS ActualsTotalQuantity,
|
|
(CASE B.TotalCost
|
|
WHEN 0 THEN NULL
|
|
ELSE ABS((B.TotalCost - C.TotalCost) / B.TotalCost)
|
|
END) AS VariationPercent,
|
|
(CASE B.TotalQuantity
|
|
WHEN 0 THEN NULL
|
|
ELSE ABS((B.TotalQuantity - C.TotalQuantity) / B.TotalQuantity)
|
|
END) AS VariationQuantityPercent
|
|
FROM VW_ScenarioRelations A
|
|
LEFT JOIN VW_ScenarioForecastTotalCost B ON (A.ForecastScenarioId = B.ForecastScenarioId)
|
|
LEFT JOIN VW_ScenarioActualsTotalCost C ON ((A.ForecastScenarioId = C.ForecastScenarioId)
|
|
AND (A.ActualsScenarioId = C.ActualsScenarioId))
|
|
|
|
GO |