/*
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].[SC_TotalCGSeatsAcrossProject]') AND type in (N'V'))
DROP VIEW [dbo].[SC_TotalCGSeatsAcrossProject]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[SC_TotalCGSeatsAcrossProject]
AS
	SELECT SD.ParentId AS ScenarioId,
	       SD.WeekEndingDate AS WeekEndingDate,
	       SUM(SD.Quantity) AS Quantity,
	       SUM(SD.Cost) AS Cost,
	       EC.CGEFX AS CGEFX,
	       SC.Type AS ScenarioType,
	       SC.Name AS ScenarioName,
	       SH.ID AS ProjectObjectId,
	       SH.Name AS ProjectName,
	       SH.TypeId AS ProjectTypeId,
	       LS.Id AS ShowStatusId,
	       LS.Name AS ProjectStatusName,
	       SH.Probability,
	       SH.Color AS ProjectColor,
	       SC.Color As ScenarioColor,
	       SC.Status AS Status,
	       SC.SystemAttributeObjectID	
	  FROM Scenario_Detail SD, Expenditure_Category EC, Scenario SC, Project SH, Status LS
	 WHERE (EC.Id = SD.ExpenditureCategoryId)
	   AND (EC.Type = 1)
	   AND (SC.Type > 1)
	   AND (SC.Id = SD.ParentId)
	   AND (SH.Id = SC.ParentId)
	   AND (LS.Id = SH.StatusId)
	 GROUP BY SD.ParentId, SD.WeekEndingDate, EC.CGEFX, 
		  SC.Type, SC.Status, SC.Name, SH.Probability, SC.Color, SC.SystemAttributeObjectID, 
		  SH.Id, SH.Name, SH.Id, SH.Color, SH.TypeId,
		  LS.Id, LS.Name
GO