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