29 lines
1.4 KiB
Transact-SQL
29 lines
1.4 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[VW_ProjectPerformance] Script Date: 23.07.2015 21:56:51 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
ALTER VIEW [dbo].[VW_ProjectPerformance]
|
|
AS
|
|
SELECT A.ForecastScenarioId, A.ActualsScenarioId, A.ForecastScenarioType, A.ForecastTotalCost, A.ActualsTotalCost, A.VariationPercent, C.Id AS ProjectId,
|
|
C.Name AS ProjectName, B.Status, B.BUDirectCosts, B.EndDate AS ScenarioEndDate, C.TypeId AS ProjectTypeId, D.Name AS ProjectTypeName,
|
|
C.Priority AS ProjectPriority, B.BUDirectCosts - A.ForecastTotalCost + A.ActualsTotalCost AS ActualsCostForecasted,
|
|
A.ActualsTotalCost - A.ForecastTotalCost AS ActualsCostVariation, 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
|
|
|
|
|