28 lines
1.2 KiB
Transact-SQL
28 lines
1.2 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
ALTER VIEW [dbo].[VW_ProjectPerformance]
|
|
AS
|
|
SELECT
|
|
A.ForecastScenarioId, A.ActualsScenarioId, A.ForecastScenarioType,
|
|
A.ForecastTotalCost, A.ActualsTotalCost, A.VariationPercent,
|
|
A.ForecastTotalQuantity, A.ActualsTotalQuantity, A.VariationQuantityPercent,
|
|
B.Status, B.BUDirectCosts, B.EndDate AS ScenarioEndDate,
|
|
C.Id AS ProjectId, C.Name AS ProjectName, C.TypeId AS ProjectTypeId, C.Priority AS ProjectPriority,
|
|
D.Name AS ProjectTypeName,
|
|
B.BUDirectCosts - A.ForecastTotalCost + A.ActualsTotalCost AS ActualsCostForecasted,
|
|
A.ActualsTotalCost - A.ForecastTotalCost AS ActualsCostVariation,
|
|
A.ActualsTotalQuantity - A.ForecastTotalQuantity AS ActualsQuantityVariation,
|
|
B.BUDirectCosts / C.Priority AS ProjectWeight,
|
|
E.Id AS FiscalPeriodId,
|
|
S2G.GroupId AS ScenarioGroupId
|
|
FROM dbo.VW_ScenarioPerformance AS A INNER JOIN
|
|
dbo.Scenario AS B ON A.ForecastScenarioId = B.Id INNER JOIN
|
|
dbo.Project AS C ON B.ParentId = C.Id INNER JOIN
|
|
dbo.Type AS D ON C.TypeId = D.Id INNER JOIN
|
|
dbo.FiscalCalendar AS E ON B.EndDate >= E.StartDate AND B.EndDate <= E.EndDate LEFT OUTER JOIN
|
|
dbo.Scenario2Group AS S2G ON B.Id = S2G.ScenarioId
|
|
WHERE
|
|
(B.Status = 1) AND (B.Type <> 9) AND (E.Type = 2)
|
|
|
|
GO |