30 lines
1.7 KiB
Transact-SQL
30 lines
1.7 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[VW_Scenario2Project] Script Date: 03.03.2015 1:19:06 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
ALTER VIEW [dbo].[VW_Scenario2Project]
|
|
AS
|
|
SELECT DISTINCT
|
|
SC.Id, SC.ParentId, SC.TemplateId, SC.Type, SC.Name,
|
|
CASE WHEN SH.IsRevenueGenerating = 1 THEN SC.ProjectedRevenue ELSE SC.ProjectedExpense + ((isnull(SC.ExpectedGrossMargin, 0) / 100)
|
|
* isnull(SC.BUDirectCosts, 0)) END AS ProjectedRevenue, SC.ExpectedGrossMargin, SC.CalculatedGrossMargin, SC.CGSplit, SC.EFXSplit, SC.StartDate, SC.EndDate,
|
|
SC.Duration, SH.Priority, SH.Probability, SC.TDDirectCosts, SC.BUDirectCosts, SC.Shots, SC.TDRevenueShot, SC.BURevenueShot, SC.FreezeRevenue,
|
|
SC.LastUpdate, SH.Id AS ShowObjectID, SH.Name AS ShowName, SH.CompanyId, SH.ClientId, SH.TypeId, SH.StatusId, SC.Color, SC.Status, SC.UseLMMargin,
|
|
SC.ExpectedGrossMargin_LM, SC.CalculatedGrossMargin_LM, SC.TDDirectCosts_LM, SC.BUDirectCosts_LM, SC.BURevenueShot_LM, SC.ShotStartDate,
|
|
SC.EntryTimeStamp, SC.GrowthScenario, SC.Actuals_BUDirectCosts, SC.Actuals_BUDirectCosts_LM, SC.SystemAttributeObjectID, s2g.GroupId,
|
|
SC.CostSavings, ParentProject.Id as ParentProjectId, ParentProject.Name as ParentProjectName, ParentProject.Color as ParentProjectColor
|
|
FROM dbo.Project AS SH
|
|
LEFT OUTER JOIN dbo.Project as ParentProject ON ParentProject.Id = SH.ParentProjectId
|
|
RIGHT OUTER JOIN dbo.Scenario AS SC ON SH.Id = SC.ParentId
|
|
LEFT OUTER JOIN dbo.Scenario2Group AS s2g ON s2g.ScenarioId = SC.Id
|
|
INNER JOIN dbo.Type AS t ON t.Id = SH.TypeId
|
|
|
|
|
|
GO |