USE $(DATA_BASE_NAME);
GO

DROP VIEW [drtrpt].[DartRequest]
GO

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

CREATE view [drtrpt].[DartRequest]
as

with evntCTE as (select RequestID
              , Max(Case when EventtypeID = 1 then Createdon end) as InitiatedDate 
              , Max(Case when EventtypeID = 2 then Createdon end) as SubmittedDate
              , Max(Case when EventtypeID = 10 then Createdon end) as ApprovalDate
From hib.Event group by RequestID)

select 
                act.ID as ResearchStudyID
                , req.ID as RequestID
                ,    req.TrackingNumber
                , at.name as ActivityType
                ,  rt.Name as RequestType
                , case when req.amendment = 1 then 'yes' else 'no' end as isAmendment
                , req.[Name] as StudyShortName
                , req.IRBNumber
                , req.IRBExpiration as IRBTerminationDate
                , act.StartDate as StudyStartDate
                , act.EndDate as StudyEndDate
                , e.initiatedDate
                , e.SubmittedDate
                , e.ApprovalDate
                , l.Name as PrimaryLocation
                , pers.FullName as PrimaryInvestigatorAtPrimarySite
                , rs.Name RequestStatus
                , partCount.Cnt as ParticipantCount
                , locCount.Cnt as LocationCount
                , req.RealSSNFlag
                , req.ScrambledSSNFlag
                , req.PHIDataFlag
from hib.Activity act
       inner join hib.activityType at on act.type = at.ID
       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 hib.participant part on (req.ID = part.RequestID and part.LocationID = req.PrimarySiteID and PrincipalInvestigator = 1)
       left join hib.location l on l.ID = req.PrimarySiteID
       left join hib.person pers on part.PersonID = pers.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
       left join evntCTE e on req.ID = e.RequestID
where rt.Name = 'Dart Request'
and req.CreatedBy not in ('vhaisltrautt','vhaislsalisc','vhaislfordl')
--order by act.ID, req.CreatedOn



GO
