USE [EnVisage]
GO

if exists (select * from sys.views where name='VW_Skill2ResourceRecentExt')
begin
  drop view VW_Skill2ResourceRecentExt
end
GO

if exists (select * from sys.views where name='VW_Skill2ResourceRecent')
begin
  drop view VW_Skill2ResourceRecent
end
GO

if exists (select * from sys.views where name='VW_LastSkill2ResourceRecords')
begin
  drop view VW_LastSkill2ResourceRecords
end
GO

if exists (select * from sys.views where name='VW_Skill2Resource')
begin
  drop view VW_Skill2Resource
end
GO

Create view VW_Skill2Resource AS
SELECT A.*, 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, E.CompanyId
FROM Skill2Resource A 
INNER JOIN Skill B ON (B.Id = A.SkillId)
INNER JOIN PeopleResource 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