66 lines
6.2 KiB
Transact-SQL
66 lines
6.2 KiB
Transact-SQL
Use [EnVisage]
|
||
|
||
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 A.NonProjectTimeId, R2T.TeamId, A.PeopleResourceId, B.WeekEndingDate, B.HoursOff, P.ExpenditureCategoryId,
|
||
T.NonProjectTimeCategoryId
|
||
FROM NonProjectTime2Resource A
|
||
INNER JOIN PeopleResource P ON (P.Id = A.PeopleResourceId)
|
||
INNER JOIN NonProjectTimeResourceAllocation B ON (B.NonProjectTime2ResourceId = A.Id)
|
||
INNER JOIN NonProjectTime T ON (T.Id = A.NonProjectTimeId)
|
||
-- Get Resource Team for every Weekending of the NPT-item
|
||
LEFT JOIN PeopleResource2Team R2T ON (R2T.PeopleResourceId = A.PeopleResourceId) AND (R2T.StartDate < B.WeekEndingDate) AND
|
||
((R2T.EndDate IS NULL) OR (R2T.EndDate >= B.WeekEndingDate))
|
||
WHERE ((P.EndDate IS NULL) OR (B.WeekEndingDate <= P.EndDate))
|
||
/* Source view:
|
||
ALTER VIEW [dbo].[VW_NonProjectTimeResourceAllocation] AS
|
||
SELECT A.NonProjectTimeId, R2T.TeamId, A.PeopleResourceId, B.WeekEndingDate, B.HoursOff, P.ExpenditureCategoryId,
|
||
T.NonProjectTimeCategoryId
|
||
FROM NonProjectTime2Resource A
|
||
INNER JOIN PeopleResource P ON (P.Id = A.PeopleResourceId)
|
||
INNER JOIN NonProjectTimeResourceAllocation B ON (B.NonProjectTime2ResourceId = A.Id)
|
||
INNER JOIN NonProjectTime T ON (T.Id = A.NonProjectTimeId)
|
||
-- Get Resource Team for every Weekending of the NPT-item
|
||
INNER JOIN PeopleResource2Team R2T ON (R2T.PeopleResourceId = A.PeopleResourceId) AND (R2T.StartDate < B.WeekEndingDate) AND
|
||
((R2T.EndDate IS NULL) OR (R2T.EndDate >= B.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, P.Name as NonProjectTimeName, M.TeamId, M.PeopleResourceId, M.WeekEndingDate, ISNULL(SUM(M.HoursOff), 0) AS HoursOff,
|
||
M.ExpenditureCategoryId, M.NonProjectTimeCategoryId, Cat.Name as NonProjectTimeCategoryName, P.IsTeamMode AS IsTeamNonProjectTime
|
||
FROM MixedAllocationData M
|
||
INNER JOIN NonProjectTime P ON (M.NonProjectTimeId = P.Id)
|
||
INNER JOIN NonProjectTimeCategory Cat on Cat.Id = M.NonProjectTimeCategoryId
|
||
GROUP BY M.NonProjectTimeId, P.Name, M.TeamId, M.PeopleResourceId, M.WeekEndingDate, M.ExpenditureCategoryId,
|
||
M.NonProjectTimeCategoryId, Cat.Name, P.IsTeamMode
|
||
GO
|
||
PRINT 'VW_NonProjectTimeAllocation created'
|
||
GO
|