55 lines
1.9 KiB
Transact-SQL
55 lines
1.9 KiB
Transact-SQL
USE EnVisage
|
|
GO
|
|
|
|
ALTER VIEW [dbo].[VW_ProjectPerformance]
|
|
|
|
AS
|
|
|
|
SELECT
|
|
|
|
scenarioPerformance.ForecastScenarioId, scenarioPerformance.ActualsScenarioId, scenarioPerformance.ForecastScenarioType,
|
|
|
|
scenarioPerformance.ForecastTotalCost, scenarioPerformance.ActualsTotalCost, scenarioPerformance.VariationPercent,
|
|
|
|
scenarioPerformance.ForecastTotalQuantity, scenarioPerformance.ActualsTotalQuantity, scenarioPerformance.VariationQuantityPercent,
|
|
|
|
scenario.Status, scenario.BUDirectCosts, scenario.EndDate AS ScenarioEndDate, scenario.StartDate ScenarioStartDate,
|
|
|
|
proj.Id AS ProjectId, proj.Name AS ProjectName, proj.TypeId AS ProjectTypeId, proj.Priority AS ProjectPriority, proj.StatusId,
|
|
|
|
projType.Name AS ProjectTypeName,
|
|
|
|
sg2Proj.StrategicGoalId,
|
|
tl.TagID,
|
|
|
|
scenario.BUDirectCosts - scenarioPerformance.ForecastTotalCost + scenarioPerformance.ActualsTotalCost AS ActualsCostForecasted,
|
|
|
|
scenarioPerformance.ActualsTotalCost - scenarioPerformance.ForecastTotalCost AS ActualsCostVariation,
|
|
|
|
scenarioPerformance.ActualsTotalQuantity - scenarioPerformance.ForecastTotalQuantity AS ActualsQuantityVariation,
|
|
|
|
scenario.BUDirectCosts / proj.Priority AS ProjectWeight,
|
|
|
|
fiscalCalendar.Id AS FiscalPeriodId,
|
|
|
|
S2G.GroupId AS ScenarioGroupId
|
|
|
|
FROM dbo.VW_ScenarioPerformance AS scenarioPerformance
|
|
|
|
INNER JOIN dbo.Scenario AS scenario ON scenarioPerformance.ForecastScenarioId = scenario.Id
|
|
|
|
INNER JOIN dbo.Project AS proj ON scenario.ParentId = proj.Id
|
|
|
|
INNER JOIN dbo.Type AS projType ON proj.TypeId = projType.Id
|
|
|
|
INNER JOIN dbo.FiscalCalendar AS fiscalCalendar ON scenario.EndDate >= fiscalCalendar.StartDate AND scenario.EndDate <= fiscalCalendar.EndDate
|
|
|
|
LEFT JOIN dbo.StrategicGoal2Project AS sg2Proj ON sg2Proj.ProjectId = proj.Id
|
|
|
|
LEFT JOIN dbo.Scenario2Group AS S2G ON scenario.Id = S2G.ScenarioId
|
|
|
|
LEFT JOIN dbo.TagLink AS tl ON proj.Id = tl.ParentID
|
|
|
|
WHERE
|
|
|
|
(scenario.Status = 1) AND (scenario.Type <> 9) AND (fiscalCalendar.Type = 2) |