61 lines
3.7 KiB
Transact-SQL
61 lines
3.7 KiB
Transact-SQL
/*
|
||
Run this script on SQL Server 2008 or later. There may be flaws if running on earlier versions of SQL Server.
|
||
*/
|
||
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VW_Scenario2Project]') AND type in (N'V'))
|
||
DROP VIEW [dbo].[VW_Scenario2Project]
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
CREATE VIEW [dbo].[VW_Scenario2Project]
|
||
AS
|
||
SELECT distinct SC.Id,
|
||
SC.ParentId,
|
||
SC.TemplateId,
|
||
SC.Type,
|
||
SC.Name,
|
||
case when isnull(t.IsRevenueGenerating, 0) = 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 as GroupId
|
||
FROM Project SH RIGHT OUTER JOIN Scenario SC ON SH.Id = SC.ParentId
|
||
LEFT JOIN Scenario2Group s2g ON s2g.ScenarioId = SC.Id
|
||
JOIN [Type] t ON t.Id = SH.TypeId
|
||
GO
|