30 lines
1.6 KiB
Transact-SQL
30 lines
1.6 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[VW_Scenario2Project] Script Date: 26.02.2015 12:56:29 ******/
|
|
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
|
|
FROM dbo.Project AS SH 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
|
|
|
|
|