57 lines
1.7 KiB
Transact-SQL
57 lines
1.7 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
|
|
|
|
if exists (select 1 from sys.views where name like 'SC_TotalSeatsAcrossExpCatsCln')
|
|
begin
|
|
DROP VIEW [dbo].[SC_TotalSeatsAcrossExpCatsCln]
|
|
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,
|
|
SG.GroupId,
|
|
EC.SystemAttributeOne,
|
|
EC.SystemAttributeTwo
|
|
FROM Scenario SC
|
|
LEFT JOIN Scenario2Group SG ON SG.ScenarioId = SC.ID
|
|
JOIN Scenario_Detail SD ON SC.ID = SD.ParentID
|
|
JOIN Project SH ON SC.ParentID = SH.ID
|
|
JOIN [Status] LS ON LS.ID = SH.StatusID
|
|
JOIN Expenditure_Category 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 |