USE [envisage]
GO
/****** Object:  View [dbo].[SC_TotalCGSeatsAcrossProject]    Script Date: 07/06/2014 11:45:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER 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,
	       SC.LowProbability AS LowProbability,
	       SC.ExpectedProbability AS ExpectedProbability,
	       SC.HighProbability AS HighProbability,
	       SH.Color AS ProjectColor,
	       SC.Color As ScenarioColor,
	       SC.UseProbability As UseProbability,
	       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, SC.LowProbability, SC.ExpectedProbability, SC.HighProbability, 
		  SC.UseProbability, SC.Color, SC.SystemAttributeObjectID, 
		  SH.Id, SH.Name, SH.Id, SH.Color, SH.TypeId,
		  LS.Id, LS.Name