USE $(DATA_BASE_NAME);
GO

DROP VIEW [dm].[vwDartRequestDetailsReport]
GO

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

Create view [dm].[vwDartRequestDetailsReport]
as

select req.TrackingNumber
                ,  rt.Name as TypeofRequest
                , act.[name] as ProjectName
                , req.[Name] as StudyShortName
                , req.IRBNumber
                , req.IRBExpiration as IRBTerminationDate
                , act.StartDate as StudyStartDate
                , act.EndDate as StudyEndDate
                , rs.Name RequestStatus
                , partCount.Cnt as ParticipantCount
                , locCount.Cnt as LocationCount
from hib.Activity act
       inner join hib.Request req on act.ID = req.ActivityID
       inner join hib.RequestType rt on req.RequestType = rt.ID
       inner join hib.RequestStatus rs on req.state = rs.ID
       left join (select RequestID, Count(1) cnt from hib.Participant group by RequestID) partCount on req.ID = partCount.RequestID
       left join (select RequestID, Count(1) cnt from hib.RequestLocation rl group by requestID ) locCount on req.ID = locCount.RequestID



GO
