44 lines
1.3 KiB
Transact-SQL
44 lines
1.3 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[SC_TotalCGSeatsAcrossProject] Script Date: 01.08.2014 11:35:20 ******/
|
|
if exists (select 1 from sys.views where name like 'SC_TotalSeatsAcrossExpCats')
|
|
begin
|
|
DROP VIEW [dbo].[SC_TotalSeatsAcrossExpCats]
|
|
end
|
|
GO
|
|
|
|
|
|
CREATE VIEW [dbo].[SC_TotalSeatsAcrossExpCats]
|
|
AS
|
|
SELECT SD.ParentID AS ScenarioObjectID,
|
|
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 ShowObjectID,
|
|
SH.Name AS ShowName,
|
|
SH.TypeID AS ShowTypeObjectID,
|
|
LS.ID AS ShowStatusObjectID,
|
|
LS.Name AS ShowStatusName,
|
|
SH.Probability,
|
|
SH.Color AS ShowColor,
|
|
SC.Color AS ScenarioColor,
|
|
EC.ID AS ExpenditureCatObjectID,
|
|
SC.Status,
|
|
SC.SystemAttributeObjectID
|
|
FROM [Status] LS, Project SH, Scenario SC, Expenditure_Category EC, Scenario_Detail SD
|
|
WHERE (SC.Type > 1)
|
|
AND (LS.ID = SH.StatusID)
|
|
AND (SC.ParentID = SH.ID)
|
|
AND (SC.ID = SD.ParentID)
|
|
AND (EC.ID = SD.ExpenditureCategoryID)
|
|
GROUP BY SD.WeekEndingDate, SC.Type, SC.Status, SD.ParentID, SC.Name, SC.Color, SH.Probability,
|
|
SC.SystemAttributeObjectID,
|
|
SH.ID, SH.Name, SH.TypeID, SH.Color,
|
|
EC.CGEFX, EC.ID,
|
|
LS.ID, LS.Name
|
|
|
|
GO |