54 lines
1.5 KiB
Transact-SQL
54 lines
1.5 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[SC_TotalSeatsAcrossExpCats] Script Date: 11.11.2014 14:09:25 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
ALTER 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,
|
|
SG.GroupId,
|
|
EC.SystemAttributeOne,
|
|
EC.SystemAttributeTwo
|
|
FROM Scenario SC
|
|
LEFT JOIN Scenario2Group SG ON SG.ScenarioId = SC.ID
|
|
JOIN ScenarioDetail SD ON SC.ID = SD.ParentID
|
|
JOIN Project SH ON SC.ParentID = SH.ID
|
|
JOIN [Status] LS ON LS.ID = SH.StatusID
|
|
JOIN ExpenditureCategory EC ON EC.ID = SD.ExpenditureCategoryID
|
|
|
|
WHERE (SC.Type > 1)
|
|
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, SG.GroupId,
|
|
EC.SystemAttributeOne,
|
|
EC.SystemAttributeTwo
|
|
|
|
|
|
GO
|
|
|
|
|