USE [EnVisage]
GO

/****** Object:  View [dbo].[SC_TotalCGSeatsAcrossProject]    Script Date: 11.11.2014 14:05:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER 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 ScenarioDetail SD, ExpenditureCategory 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