79 lines
7.5 KiB
Transact-SQL
79 lines
7.5 KiB
Transact-SQL
Use [EnVisage]
|
||
|
||
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_NonProjectTimeInvalidItems')
|
||
BEGIN
|
||
DROP VIEW VW_NonProjectTimeInvalidItems
|
||
PRINT 'VW_NonProjectTimeInvalidItems dropped'
|
||
END
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_NonProjectTimeAllocation')
|
||
BEGIN
|
||
DROP VIEW VW_NonProjectTimeAllocation
|
||
PRINT 'VW_NonProjectTimeAllocation dropped'
|
||
END
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_NonProjectTimeResourceAllocation')
|
||
BEGIN
|
||
DROP VIEW VW_NonProjectTimeResourceAllocation
|
||
PRINT 'VW_NonProjectTimeResourceAllocation dropped'
|
||
END
|
||
GO
|
||
|
||
CREATE VIEW [dbo].[VW_NonProjectTimeResourceAllocation] AS
|
||
SELECT NPT2RES.NonProjectTimeId, R2T.TeamId, NPT2RES.PeopleResourceId, NPTAL.WeekEndingDate, NPTAL.HoursOff, RES.ExpenditureCategoryId,
|
||
NPT.NonProjectTimeCategoryId
|
||
FROM NonProjectTime2Resource NPT2RES
|
||
INNER JOIN PeopleResource RES ON (RES.Id = NPT2RES.PeopleResourceId)
|
||
INNER JOIN NonProjectTimeResourceAllocation NPTAL ON (NPTAL.NonProjectTime2ResourceId = NPT2RES.Id)
|
||
INNER JOIN NonProjectTime NPT ON (NPT.Id = NPT2RES.NonProjectTimeId)
|
||
-- Get Resource Team for every Weekending of the NPT-item
|
||
INNER JOIN PeopleResource2Team R2T ON (R2T.PeopleResourceId = NPT2RES.PeopleResourceId) AND (R2T.StartDate < NPTAL.WeekEndingDate) AND
|
||
((R2T.EndDate IS NULL) OR (R2T.EndDate >= NPTAL.WeekEndingDate))
|
||
GO
|
||
PRINT 'VW_NonProjectTimeResourceAllocation created'
|
||
GO
|
||
|
||
CREATE VIEW [dbo].[VW_NonProjectTimeAllocation] AS
|
||
WITH MixedAllocationData (NonProjectTimeId, TeamId, PeopleResourceId, WeekEndingDate, HoursOff, ExpenditureCategoryId,
|
||
NonProjectTimeCategoryId) AS
|
||
(
|
||
SELECT NonProjectTimeId, TeamId, PeopleResourceId, WeekEndingDate, HoursOff, ExpenditureCategoryId,
|
||
NonProjectTimeCategoryId
|
||
FROM VW_NonProjectTimeResourceAllocation
|
||
UNION ALL
|
||
SELECT NonProjectTimeId, TeamId, PeopleResourceId, WeekEndingDate, HoursOff, ExpenditureCategoryId,
|
||
NonProjectTimeCategoryId
|
||
FROM VW_NonProjectTimeTeamAllocation
|
||
)
|
||
SELECT M.NonProjectTimeId, NPT.Name as NonProjectTimeName, M.TeamId, M.PeopleResourceId, M.WeekEndingDate, ISNULL(SUM(M.HoursOff), 0) AS HoursOff,
|
||
M.ExpenditureCategoryId, M.NonProjectTimeCategoryId, Cat.Name as NonProjectTimeCategoryName, NPT.IsTeamMode AS IsTeamNonProjectTime
|
||
FROM MixedAllocationData M
|
||
INNER JOIN NonProjectTime NPT ON (M.NonProjectTimeId = NPT.Id)
|
||
INNER JOIN NonProjectTimeCategory Cat on Cat.Id = M.NonProjectTimeCategoryId
|
||
GROUP BY M.NonProjectTimeId, NPT.Name, M.TeamId, M.PeopleResourceId, M.WeekEndingDate, M.ExpenditureCategoryId,
|
||
M.NonProjectTimeCategoryId, Cat.Name, NPT.IsTeamMode
|
||
GO
|
||
PRINT 'VW_NonProjectTimeAllocation created'
|
||
GO
|
||
|
||
|
||
CREATE VIEW VW_NonProjectTimeInvalidItems AS
|
||
SELECT NPT2RES.NonProjectTimeId, NPT.Name as NonProjectTimeName, NPT.StartDate, NPT.EndDate,
|
||
NPT2RES.PeopleResourceId, RES.ExpenditureCategoryId, NPT.NonProjectTimeCategoryId,
|
||
NPTCAT.Name as NonProjectTimeCategoryName, NPT.Details
|
||
FROM NonProjectTime2Resource NPT2RES
|
||
INNER JOIN PeopleResource RES ON (RES.Id = NPT2RES.PeopleResourceId)
|
||
INNER JOIN NonProjectTimeResourceAllocation NPTAL ON (NPTAL.NonProjectTime2ResourceId = NPT2RES.Id)
|
||
INNER JOIN NonProjectTime NPT ON (NPT.Id = NPT2RES.NonProjectTimeId)
|
||
INNER JOIN NonProjectTimeCategory NPTCAT ON (NPTCAT.Id = NPT.NonProjectTimeCategoryId)
|
||
LEFT JOIN PeopleResource2Team R2T ON (R2T.PeopleResourceId = NPT2RES.PeopleResourceId) AND
|
||
(R2T.StartDate <= NPTAL.WeekEndingDate) AND ((R2T.EndDate IS NULL) OR (R2T.EndDate >= NPTAL.WeekEndingDate))
|
||
WHERE (NPT.Permanent = 0) AND R2T.TeamId IS NULL
|
||
GROUP BY NPT2RES.NonProjectTimeId, NPT.Name, NPT2RES.PeopleResourceId, NPT.StartDate, NPT.EndDate,
|
||
RES.ExpenditureCategoryId, NPT.NonProjectTimeCategoryId, NPTCAT.Name, NPT.Details
|
||
GO
|
||
PRINT 'VW_NonProjectTimeInvalidItems created'
|
||
GO
|