USE $(DATA_BASE_NAME);
GO

DROP VIEW [drtrpt].[DartRequestParticipants]
GO

/****** Object:  View [drtrpt].[DartRequestParticipants]    Script Date: 2/24/2016 1:58:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE view [drtrpt].[DartRequestParticipants]
as

select req.ActivityID as ResearchStudyID
              , req.ID as RequestID
              , req.TrackIngNumber
              , loc.Name as LocationName
              , case when req.PrimarySiteID = part.LocationID then 'Primary' else 'Secondary' end as LocationType
              , pers.FullName as ParticipantName
              , case when part.PrincipalInvestigator = 1 then 'yes' else 'no' end as PrincipalInvestigatorForSite
              , case when part.DataAccess = 1 then 'yes' else 'no' end as HasDataAccess
              , case when part.[Notification] = 1 then 'yes' else 'no' end  as GetsNotification
from hib.Request req
       left join hib.participant part on req.ID = part.RequestID
       left join hib.person pers on part.PersonID = pers.ID
       left join hib.Location loc on part.LocationID = loc.ID
--order by req.ActivityID, req.TrackingNumber


GO
