27 lines
2.1 KiB
Transact-SQL
27 lines
2.1 KiB
Transact-SQL
Use [EnVisage]
|
||
|
||
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_ResourceExpenditureCategoryWithSuperEC')
|
||
BEGIN
|
||
DROP VIEW VW_ResourceExpenditureCategoryWithSuperEC
|
||
PRINT 'VW_ResourceExpenditureCategoryWithSuperEC dropped'
|
||
END
|
||
GO
|
||
|
||
CREATE VIEW VW_ResourceExpenditureCategoryWithSuperEC AS
|
||
select Id, Name, TeamId, TeamStartDate, TeamEndDate
|
||
from dbo.VW_ResourceExpenditureCategory
|
||
union all
|
||
select cast(ec.Id as varchar(36)), ec.Name, cast(t2p.TeamId as varchar(36)), pr2t.StartDate, pr2t.EndDate
|
||
from ExpenditureCategory ec
|
||
join ScenarioDetail sd on sd.ExpenditureCategoryId = ec.Id
|
||
join Scenario s on s.Id = sd.ParentID and s.Status = 1
|
||
join Project proj on proj.Id = s.ParentId
|
||
join Team2Project t2p on t2p.ProjectId = proj.Id
|
||
join PeopleResource2Team pr2t on pr2t.TeamId = t2p.TeamId
|
||
join PeopleResourceAllocation pra on (pra.PeopleResourceId = pr2t.PeopleResourceId) and
|
||
(pra.TeamId = t2p.TeamId) and (s.Id = pra.ScenarioId) and (pra.Quantity <> 0)
|
||
where ec.AllowResourceAssignment = 0
|
||
GO
|
||
PRINT 'VW_ResourceExpenditureCategoryWithSuperEC created'
|
||
GO
|