/* Updated 12/14/2015:

It just needs to be updated to 9/30/2015 (ie up to the end of FY15) and the following items added…

1.	New Tab for “NDS Approved Research Requests”:  research requests that have been submitted for review and approved by NDS

Thanks!
Jeff
*/

/* Original: 8/17/2015:

Can you send me a report from DART that includes:

Site, total # of research projects for that specific site, total # of participants at that specific site. 

Is there a way to not duplicate the participants. For example, the same person may be working on 5 different projects at that site. I don’t want that person counted 5 times.

Can you order them by site with the most projects….site with the fewest projects.

PII, PhD, MSRegSci, CIPP/G
VASLCHCS/OIFO/VINCI
PII
Office: PII
*/


------------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------

--Site, total # of research projects for that specific site, total # of participants at that specific site. 
--Is there a way to not duplicate the participants. For example, the same person may be working on 5 different projects at that site. I don’t want that person counted 5 times.
--Can you order them by site with the most projects….site with the fewest projects.


if OBJECT_ID('tempdb..#siteprojectparticipant') is not null
	drop table #siteprojectparticipant

Create table #siteprojectparticipant (
	LocationName varchar(64),
	LocationID int,

	NumRequestsAll int,
	NumParticipantsAll int,

	NumRequestsSubmitted int,
	NumParticipantsSubmitted int,

	NumRequestsApproved int,
	NumParticipantsApproved int
)


--ALL RESEARCH REQUESTS (including "not yet submitted" requests)
insert into #siteprojectparticipant(LocationName,LocationID,NumRequestsAll,NumParticipantsAll,NumRequestsSubmitted,NumParticipantsSubmitted,NumRequestsApproved,NumParticipantsApproved)
select loc.[name] as 'Location Name',
		loc.id as 'Location ID',
	   case when numRequests is not null then numRequests
	        else 0 end as 'Number of Requests',
	   case when numUniqueParticipants is not null then numUniqueParticipants 
			else 0 end as 'Number of Unique Participants',
		0,0,0,0
from hib.location loc
	left join (select locationid,count(requestid) as numRequests from hib.RequestLocation rl left join hib.request r on rl.requestid=r.id where r.requesttype=2 and (cast(r.createdon as date) >= '2014-10-01' and cast(r.createdon as date) <= '2015-09-30') group by locationid) locationRequests on loc.id=locationRequests.locationid
	left join (select locationid,count(personid) as numUniqueParticipants from (select p.locationid as locationId,p.personid as personId from hib.participant p left join hib.request r on p.requestid=r.id where r.requesttype=2  and (cast(r.createdon as date) >= '2014-10-01' and cast(r.createdon as date) <= '2015-09-30') group by p.locationid,p.personid) p group by locationid) locationParticipants on loc.id=locationParticipants.locationid
--order by numRequests desc
order by loc.id


--SUBMITTED RESEARCH REQUESTS (requires a submitted event)
insert into #siteprojectparticipant(LocationName,LocationID,NumRequestsAll,NumParticipantsAll,NumRequestsSubmitted,NumParticipantsSubmitted,NumRequestsApproved,NumParticipantsApproved)
select loc.[name] as 'Location Name',
		loc.id as 'Location ID',
		0,0,
	   case when numRequests is not null then numRequests
	        else 0 end as 'Number of Requests',
	   case when numUniqueParticipants is not null then numUniqueParticipants 
			else 0 end as 'Number of Unique Participants',
		0,0
from hib.location loc
	left join (select locationid,count(rl.requestid) as numRequests from hib.RequestLocation rl left join hib.request r on rl.requestid=r.id inner join drtrpt.dartrequestEvent e on (e.requestid=r.id and e.EventType='Submitted') where r.requesttype=2 and (cast(e.EventDate as date) >= '2014-10-01' and cast(e.EventDate as date) <= '2015-09-30') group by locationid) locationRequests on loc.id=locationRequests.locationid
	left join (select locationid,count(personid) as numUniqueParticipants from (select p.locationid as locationId,p.personid as personId from hib.participant p left join hib.request r on p.requestid=r.id inner join drtrpt.dartrequestEvent e on (e.requestid=r.id and e.EventType='Submitted') where r.requesttype=2 and (cast(e.EventDate as date) >= '2014-10-01' and cast(e.EventDate as date) <= '2015-09-30') group by p.locationid,p.personid) p group by locationid) locationParticipants on loc.id=locationParticipants.locationid
--order by numRequests desc
order by loc.id


--RESEARCH REQUESTS submitted and approved by NDS
insert into #siteprojectparticipant(LocationName,LocationID,NumRequestsAll,NumParticipantsAll,NumRequestsSubmitted,NumParticipantsSubmitted,NumRequestsApproved,NumParticipantsApproved)
select loc.[name] as 'Location Name',
		loc.id as 'Location ID',
		0,0,0,0,
	   case when numRequests is not null then numRequests
	        else 0 end as 'Number of Requests',
	   case when numUniqueParticipants is not null then numUniqueParticipants 
			else 0 end as 'Number of Unique Participants'
from hib.location loc
	left join (select locationid,count(rl.requestid) as numRequests from hib.RequestLocation rl left join hib.request r on rl.requestid=r.id inner join drtrpt.dartrequestEvent e on (e.requestid=r.id and e.EventDescription='Final NDS Approval Complete') where r.requesttype=2 and r.[state] in (4,7) and (cast(e.EventDate as date) >= '2014-10-01' and cast(e.EventDate as date) <= '2015-09-30') group by locationid) locationRequests on loc.id=locationRequests.locationid	--Research Requests only
	left join (select locationid,count(personid) as numUniqueParticipants from (select p.locationid as locationId,p.personid as personId from hib.participant p left join hib.request r on p.requestid=r.id inner join drtrpt.dartrequestEvent e on (e.requestid=r.id and e.EventDescription='Final NDS Approval Complete') where r.requesttype=2  and r.[state] in (4,7) and (cast(e.EventDate as date) >= '2014-10-01' and cast(e.EventDate as date) <= '2015-09-30') group by p.locationid,p.personid) p group by locationid) locationParticipants on loc.id=locationParticipants.locationid
--order by numRequests desc
order by loc.id


/* Select the final output */
select LocationName, 
	   --LocationID,
	   sum(NumRequestsAll) as NumRequestsAll,
	   sum(NumParticipantsAll) as NumParticipantsAll,
	   sum(NumRequestsSubmitted) as NumRequestsSubmitted,
	   sum(NumParticipantsSubmitted) as NumParticipantsSubmitted,
	   sum(NumRequestsApproved) as NumRequestsApproved,
	   sum(NumParticipantsApproved) as NumParticipantsApproved
from #siteprojectparticipant
group by LocationName,LocationID
--order by LocationID
--order by NumRequestsApproved desc
order by LocationName



------------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------
