46 lines
1.3 KiB
Transact-SQL
46 lines
1.3 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.objects WHERE Name = N'GetFCMaxDate')
|
|
BEGIN
|
|
DROP FUNCTION GetFCMaxDate
|
|
END
|
|
GO
|
|
|
|
CREATE FUNCTION [dbo].[GetFCMaxDate] ()
|
|
RETURNS date
|
|
AS
|
|
BEGIN
|
|
DECLARE @ret date
|
|
|
|
select @ret = MAX(EndDate) from FiscalCalendar
|
|
where ([Type] = 0)
|
|
|
|
RETURN @ret
|
|
END
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_NonProjectTimeResourceAllocation')
|
|
BEGIN
|
|
DROP VIEW VW_NonProjectTimeResourceAllocation
|
|
END
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[VW_NonProjectTimeResourceAllocation] AS
|
|
WITH ResourceTeamMembershipDates (PeopleResourceId, StartDate, EndDate) AS
|
|
(
|
|
SELECT PeopleResourceId, MIN(StartDate) AS StartDate, MAX(ISNULL(EndDate, dbo.GetFCMaxDate())) as EndDate
|
|
FROM PeopleResource2Team
|
|
GROUP BY PeopleResourceId
|
|
)
|
|
SELECT A.NonProjectTimeId, A.PeopleResourceId, B.WeekEndingDate, B.HoursOff, P.ExpenditureCategoryId,
|
|
T.NonProjectTimeCategoryId
|
|
FROM NonProjectTime2Resource A
|
|
INNER JOIN PeopleResource P ON (P.Id = A.PeopleResourceId)
|
|
INNER JOIN ResourceTeamMembershipDates M ON (M.PeopleResourceId = A.PeopleResourceId)
|
|
INNER JOIN NonProjectTimeResourceAllocation B ON (B.NonProjectTime2ResourceId = A.Id)
|
|
INNER JOIN NonProjectTime T ON (T.Id = A.NonProjectTimeId)
|
|
WHERE (B.WeekEndingDate >= M.StartDate) AND ((M.EndDate IS NULL) OR (B.WeekEndingDate <= M.EndDate))
|
|
GO
|
|
|