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