121 lines
4.0 KiB
Transact-SQL
121 lines
4.0 KiB
Transact-SQL
-- SA. Views for scenario performance indication
|
|
USE [envisage]
|
|
Go
|
|
|
|
if exists (select * from sys.views where name='VW_ScenarioPerformance')
|
|
begin
|
|
drop view VW_ScenarioPerformance
|
|
end
|
|
GO
|
|
|
|
if exists (select * from sys.views where name='VW_ScenarioForecastTotalCost')
|
|
begin
|
|
drop view VW_ScenarioForecastTotalCost
|
|
end
|
|
GO
|
|
|
|
if exists (select * from sys.views where name='VW_ScenarioActualsTotalCost')
|
|
begin
|
|
drop view VW_ScenarioActualsTotalCost
|
|
end
|
|
GO
|
|
|
|
if exists (select * from sys.views where name='VW_ScenarioPseudoActuals')
|
|
begin
|
|
drop view VW_ScenarioPseudoActuals
|
|
end
|
|
GO
|
|
|
|
if exists (select * from sys.views where name='VW_ScenarioActualsReplacePoints')
|
|
begin
|
|
drop view VW_ScenarioActualsReplacePoints
|
|
end
|
|
GO
|
|
|
|
if exists (select * from sys.views where name='VW_ScenarioRelations')
|
|
begin
|
|
drop view VW_ScenarioRelations
|
|
end
|
|
GO
|
|
|
|
if exists (select * from sys.views where name='VW_ScenarioActualsPeriod')
|
|
begin
|
|
drop view VW_ScenarioActualsPeriod
|
|
end
|
|
GO
|
|
|
|
-- Period of actuals existance for every actuals scenario
|
|
CREATE VIEW VW_ScenarioActualsPeriod AS
|
|
SELECT A.ParentId AS ScenarioId, MIN(A.WeekEndingDate) AS StartDate, MAX(A.WeekEndingDate) AS EndDate
|
|
FROM ScenarioDetail A
|
|
INNER JOIN Scenario B ON (A.ParentId = B.Id)
|
|
WHERE B.[Type] = 9
|
|
GROUP BY A.ParentID
|
|
GO
|
|
|
|
-- The corresponding actuals scenario Id for every forecast scenario
|
|
CREATE VIEW VW_ScenarioRelations AS
|
|
SELECT A.Id AS ForecastScenarioId, A.[Type] AS ForecastScenarioType, B.Id AS ActualsScenarioId
|
|
FROM Scenario A
|
|
INNER JOIN Scenario B ON (A.ParentId = B.ParentId)
|
|
WHERE A.[Type] <> 9
|
|
AND B.[Type] = 9
|
|
GO
|
|
|
|
-- Data points for every forecast scenario, that are missing in its corresponding actuals scenario
|
|
-- Forecast data from these points of scenario must be added to actuals to fill actuals gaps
|
|
CREATE VIEW VW_ScenarioActualsReplacePoints AS
|
|
SELECT A.ParentID AS ForecastScenarioId, A.ExpenditureCategoryId, A.WeekEndingDate FROM ScenarioDetail A
|
|
INNER JOIN VW_ScenarioRelations C ON (A.ParentID = C.ForecastScenarioId)
|
|
INNER JOIN VW_ScenarioActualsPeriod D ON (C.ActualsScenarioId = D.ScenarioId) AND (A.WeekEndingDate <= D.EndDate)
|
|
WHERE C.ForecastScenarioType <> 9
|
|
EXCEPT
|
|
SELECT M.ForecastScenarioId, K.ExpenditureCategoryId, K.WeekEndingDate FROM ScenarioDetail K
|
|
INNER JOIN Scenario L ON (K.ParentID = L.Id)
|
|
INNER JOIN VW_ScenarioRelations M ON (K.ParentID = M.ActualsScenarioId)
|
|
WHERE L.[Type] = 9
|
|
GO
|
|
|
|
-- Actuals with the forecast values in the actuals gaps for every project
|
|
CREATE VIEW VW_ScenarioPseudoActuals AS
|
|
SELECT A.Id, C.ActualsScenarioId, A.ExpenditureCategoryId, A.WeekEndingDate, A.Quantity,
|
|
A.Cost, 1 AS IsForecast
|
|
FROM ScenarioDetail A
|
|
INNER JOIN VW_ScenarioActualsReplacePoints B ON (A.ParentID = B.ForecastScenarioId)
|
|
AND (A.ExpenditureCategoryId = B.ExpenditureCategoryId)
|
|
AND (A.WeekEndingDate = B.WeekEndingDate)
|
|
INNER JOIN VW_ScenarioRelations C ON (A.ParentID = C.ForecastScenarioId)
|
|
UNION
|
|
SELECT M.Id, M.ParentID, M.ExpenditureCategoryId, M.WeekEndingDate, M.Quantity,
|
|
M.Cost, 0 AS IsForecast FROM ScenarioDetail M
|
|
INNER JOIN Scenario N ON (M.ParentID = N.Id)
|
|
WHERE N.[Type] = 9
|
|
GO
|
|
|
|
-- Total scenario actuals cost
|
|
CREATE VIEW VW_ScenarioActualsTotalCost AS
|
|
SELECT A.ActualsScenarioId, SUM(A.Cost) AS TotalCost
|
|
FROM VW_ScenarioPseudoActuals A
|
|
GROUP BY A.ActualsScenarioId
|
|
GO
|
|
|
|
CREATE VIEW VW_ScenarioForecastTotalCost AS
|
|
SELECT A.ParentID AS ForecastScenarioId, SUM(A.Cost) AS TotalCost
|
|
FROM ScenarioDetail A
|
|
INNER JOIN VW_ScenarioRelations B ON (A.ParentID = B.ForecastScenarioId)
|
|
INNER JOIN VW_ScenarioActualsPeriod C ON (B.ActualsScenarioId = C.ScenarioId) AND (A.WeekEndingDate <= C.EndDate)
|
|
GROUP BY A.ParentID
|
|
GO
|
|
|
|
CREATE VIEW VW_ScenarioPerformance AS
|
|
SELECT A.ForecastScenarioId, A.ActualsScenarioId, A.ForecastScenarioType,
|
|
B.TotalCost AS ForecastTotalCost, C.TotalCost AS ActualsTotalCost,
|
|
(CASE B.TotalCost
|
|
WHEN 0 THEN NULL
|
|
ELSE ABS((B.TotalCost - C.TotalCost) / B.TotalCost)
|
|
END) AS VariationPercent
|
|
FROM VW_ScenarioRelations A
|
|
LEFT JOIN VW_ScenarioForecastTotalCost B ON (A.ForecastScenarioId = B.ForecastScenarioId)
|
|
LEFT JOIN VW_ScenarioActualsTotalCost C ON (A.ActualsScenarioId = C.ActualsScenarioId)
|
|
GO
|