USE $(DATA_BASE_NAME);
GO

DROP VIEW [hib].[vwPrimaryContact]
GO

/****** Object:  View [hib].[vwPrimaryContact]    Script Date: 2/24/2016 1:58:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create View [hib].[vwPrimaryContact] 
as
with CTE as (Select x.reqID, x.FullName, 1 as ID_ from (select r.ID ReqID
                                         , loc.ID
                                         , case when r.PrimarySiteID = loc.ID then 1 else 0 end as PrimarySite
                                         , p.FullName
                                         ,Row_number() over (partition by r.ID, loc.ID order by p.FullName asc) as RID
                                         from hib.request r
                                         left join hib.Participant part on r.ID = part.RequestID
                                         left join hib.Location loc on part.LocationID = loc.ID
                                         left join hib.Person p on part.PersonID = p.ID
                           where part.PrincipalInvestigator =1) X
                           where x.RID = 1
                           and X.PrimarySite = 1

                           union 

                           select x.ReqID, x.FullName, 2 as ID_ from
                           (select r.ID as reqID, p.FullName, row_number() over (partition by r.ID order by p.FullName asc) RID
                           from hib.request r
                                  inner join hib.Participant part on r.ID = part.RequestID
                                  inner join hib.Location loc on part.LocationID = loc.ID
                                  inner join hib.Person p on part.PersonID = p.ID

                           where r.PrimarySiteId = loc.ID and part.PrincipalInvestigator = 0) X
                           where x.RID = 1
                           )

select r.iD, inv.FullName from hib.request r
left join (select c.ReqiD, c.fullname from CTE c
                           inner join (select ReqID, min(ID_) ID_ From CTE
                                                              group by ReqID ) x on c.ReqID = x.ReqID and c.ID_ = x.ID_
)      inv on r.ID = inv.reqID

GO
