59 lines
2.3 KiB
Transact-SQL
59 lines
2.3 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_NonProjectTimeAllocation')
|
|
BEGIN
|
|
DROP VIEW [dbo].[VW_NonProjectTimeAllocation]
|
|
END
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_NonProjectTimeResourceAllocation')
|
|
BEGIN
|
|
DROP VIEW [dbo].[VW_NonProjectTimeResourceAllocation]
|
|
END
|
|
GO
|
|
|
|
/****** Object: View [dbo].[VW_NonProjectTimeAllocation] Script Date: 5/6/2016 6:57:38 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
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
|
|
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
|
|
|
|
|
|
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
|
|
|
|
|