USE [EnVisage]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER view [dbo].[VW_Skill2Resource] AS
	SELECT A.Id, A.Type, A.EffectiveDate, A.SkillId, A.ResourceId, A.Level, A.Interested, A.DateCreated, 
		ISNULL(D.Id, A.SkillId) AS SkillGroupId, 
		CAST((CASE WHEN (D.Id IS NULL) THEN 1 ELSE 0 END) AS bit) AS AssignedToGroup,
		E.Id AS TeamId, C.TeamStartDate, C.TeamEndDate, E.CompanyId, 
		abs(datediff(D, dateadd(y, -1, GETUTCDATE()), A.effectivedate)) as PastYearDateOffset,
		abs(datediff(D, A.effectivedate, dateadd(y, 1, GETUTCDATE()))) as FutureYearDateOffset
	FROM Skill2Resource A 
		INNER JOIN Skill B ON (B.Id = A.SkillId)
		INNER JOIN VW_TeamResource C ON (C.Id = A.ResourceId)
		LEFT JOIN Skill D ON (D.Id = B.ParentId)
		LEFT JOIN Team E ON (E.Id = C.TeamId)
GO