/*
	Retrieves info about the requests that were APPROVED in the specified date range.
		ra.EndDate >= fromDate, ra.EndDate <= toDate 

	Retrieves the time taken by each review group and by the requestor, for each review type.
*/
select 
--			d.FiscalYear,
--			d.MonthofYear as FiscalMonth,
--			d.MonthName as [Month],
			r.TrackingNumber as NDSTrackingNumber
			, r.IRBNumber
			, case when r.amendment = 1 then 'YES' else 'NO' end  as isAmendment
--			, rt.Name as TypeOfRequest
--			, replace(act.name,char(9),'') as ProjectName
			, replace(r.Name,char(9),'') as StudyShortName
--			, r.IRBExpiration as IRBTerminationDate
--			, act.StartDate as StudyStartDate
--			, act.eNdDate as StudyEndDate
--			, rs.Name as requestStatus

			, e.createdon as InitialStartDate	--Date Request submitted for Initial Review: the first time the request was submitted (r.submittedon gets updated when the request is submitted after a change request)

			, ra2.EndDate CompletedDate	--Final NDS Approval Date

			, [dbo].[udf_GetBusinessDays](e.createdon, ra2.EndDate) as TotalNumberofProcessingDays	--TotalNumberofProcessingDays (from "Date Request submitted for Initial Review" to "Final NDS Approval Date")
--			, [dbo].[udf_GetCalendarDays](e.createdon, ra2.EndDate) as TotalNumberofCalendarDays

			, Max(Case when ra.ReviewType = 'Initial NDS' then ra.TotalReviewerBusinessDays end) as [Init NDS Reviewer Days]
--			, Max(Case when ra.ReviewType = 'Initial NDS' then ra.TotalReviewerCalendarDays end) as [Init NDS Reviewer Cal Days]
			, Max(Case when ra.ReviewType = 'Initial NDS' then ra.TotalRequestorBusinessDays end) as [Init NDS Requestor Days]
--			, Max(Case when ra.ReviewType = 'Initial NDS' then ra.TotalRequestorCalendarDays end) as [Init NDS Requestor Cal Days]

			, Max(Case when ra.ReviewType = 'Privacy' then ra.TotalReviewerBusinessDays end) as [Privacy Reviewer Days]
--			, Max(Case when ra.ReviewType = 'Privacy' then ra.TotalReviewerCalendarDays end) as [Privacy Reviewer Cal Days]
			, Max(Case when ra.ReviewType = 'Privacy' then ra.TotalRequestorBusinessDays end) as [Privacy Requestor Days]
--			, Max(Case when ra.ReviewType = 'Privacy' then ra.TotalRequestorCalendarDays end) as [Privacy Requestor Cal Days]

			, Max(Case when ra.ReviewType = 'Security' then ra.TotalReviewerBusinessDays end) as [Security Reviewer Days]
--			, Max(Case when ra.ReviewType = 'Security' then ra.TotalReviewerCalendarDays end) as [Security Reviewer Cal Days]
			, Max(Case when ra.ReviewType = 'Security' then ra.TotalRequestorBusinessDays end) as [Security Requestor Days]
--			, Max(Case when ra.ReviewType = 'Security' then ra.TotalRequestorCalendarDays end) as [Security Requestor Cal Days]

			, Max(Case when ra.ReviewType = 'ORD' then ra.TotalReviewerBusinessDays end) as [ORD Reviewer Days]
--			, Max(Case when ra.ReviewType = 'ORD' then ra.TotalReviewerCalendarDays end) as [ORD Reviewer Cal Days]
			, Max(Case when ra.ReviewType = 'ORD' then ra.TotalRequestorBusinessDays end) as [ORD Requestor Days]
--			, Max(Case when ra.ReviewType = 'ORD' then ra.TotalRequestorCalendarDays end) as [ORD Requestor Cal Days]

			, Max(Case when ra.ReviewType = 'Capri' then ra.TotalReviewerBusinessDays end) as [Capri Reviewer Days]
--			, Max(Case when ra.ReviewType = 'Capri' then ra.TotalReviewerCalendarDays end) as [Capri Reviewer Cal Days]
			, Max(Case when ra.ReviewType = 'Capri' then ra.TotalRequestorBusinessDays end) as [Capri Requestor Days]
--			, Max(Case when ra.ReviewType = 'Capri' then ra.TotalRequestorCalendarDays end) as [Capri Requestor Cal Days]

			, Max(Case when ra.ReviewType = 'OEF' then ra.TotalReviewerBusinessDays end) as [OEF Reviewer Days]
--			, Max(Case when ra.ReviewType = 'OEF' then ra.TotalReviewerCalendarDays end) as [OEF Reviewer Cal Days]
			, Max(Case when ra.ReviewType = 'OEF' then ra.TotalRequestorBusinessDays end) as [OEF Requestor Days]
--			, Max(Case when ra.ReviewType = 'OEF' then ra.TotalRequestorCalendarDays end) as [OEF Requestor Cal Days]

			, Max(Case when ra.ReviewType = 'Homeless Registry' then ra.TotalReviewerBusinessDays end) as [Homeless Registry Reviewer Days]
--			, Max(Case when ra.ReviewType = 'Homeless Registry' then ra.TotalReviewerCalendarDays end) as [Homeless Registry Reviewer Cal Days]
			, Max(Case when ra.ReviewType = 'Homeless Registry' then ra.TotalRequestorBusinessDays end) as [Homeless Registry Requestor Days]
--			, Max(Case when ra.ReviewType = 'Homeless Registry' then ra.TotalRequestorCalendarDays end) as [Homeless Registry Requestor Cal Days]
			
			, Max(Case when ra.ReviewType = 'Final NDS' then ra.TotalReviewerBusinessDays end) as [Final NDS Reviewer Days]
--			, Max(Case when ra.ReviewType = 'Final NDS' then ra.TotalReviewerCalendarDays end) as [Final NDS Reviewer Cal Days]
			, Max(Case when ra.ReviewType = 'Final NDS' then ra.TotalRequestorBusinessDays end) as [Final NDS Requestor Days]
--			, Max(Case when ra.ReviewType = 'Final NDS' then ra.TotalRequestorCalendarDays end) as [Final NDS Requestor Cal Days]

 from dm.ReviewAggregate ra
	inner join hib.Request r on ra.RequestID= r.ID
	inner join hib.RequestType rt on r.[requestType] = rt.ID
	inner join hib.Activity act on r.ActivityID = act.ID
	inner join hib.RequestStatus rs on r.[State] = rs.ID

--	inner join hib.[event] e on (e.requestid = r.id and e.description like '%Submitting Data Access Request Packet%')	--Date Request submitted for Initial Review
	inner join hib.[event] e on (e.requestid = r.id and e.eventtypeid=2)	--Date Request submitted for Initial Review

	left join dm.ReviewAggregate ra2 on ra.RequestID = ra2.RequestID	--this request was approved in the specified date range
				 and ra2.ReviewType = 'Final NDS' 
				 and ra2.CurrentStatus = 'Approved' 
				 and (cast(ra2.EndDate as date) >= '2013-10-01' and cast(ra2.EndDate as date) <= '2014-09-30')
	left join dm.Date d on cast(Convert(varchar(10),ra2.EndDate,112) as int) = d.dateSID
where ra2.RequestID is not null 
and rt.Name = 'Dart Request'	--Research requests only
group by d.FiscalYear,d.MonthofYear, d.MonthName,r.TrackingNumber, rt.Name , act.name , r.Name , r.IRBNumber, case when r.amendment = 1 then 'YES' else 'NO' end, r.IRBExpiration , act.StartDate, act.eNdDate,rs.Name,e.createdon, ra2.EndDate
order by d.FiscalYear,d.MonthofYear, d.MonthName,r.TrackingNumber

