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