/*Step 1.  
Create this table
*/
if OBJECT_ID('tempdb..#rslt') is not null
	drop table #rslt

Create table #rslt (
	DataSource varchar(128),
	FiscalYear int,
	FiscalQuarter int,
	FiscalMonth int,
	MonthName varchar(15),
	Initiated int,
	Approved int,
	ParticipantsRealSSN int,
	ParticipantsScrambledSSN int,
	ParticipantsPHI int,
	Participants int,
	Amendment bit
)


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

Create table #rsltTotal (
	DataSource varchar(128),
	FiscalYear int,
	FiscalQuarter int,
	FiscalMonth int,
	MonthName varchar(15),
	InitiatedNew int,
	InitiatedAmend int,
	Initiated int,
	ApprovedNew int,
	ApprovedAmend int,
	Approved int,
	ParticipantsNew int,
	ParticipantsAmend int,
	Participants int,
	
	ParticipantsRealNew int,
	ParticipantsRealAmend int,
	ParticipantsReal int,

	ParticipantsScrambledNew int,
	ParticipantsScrambledAmend int,
	ParticipantsScrambled int,

	ParticipantsPHINew int,
	ParticipantsPHIAmend int,
	ParticipantsPHI int
)


/*
	Step 2 - Fill dat table yo!

	!!!! change the Fiscal year - OR, if you are fancy, variablize it!
		Original:  and (e.CreatedOn >= '2013-10-01' and e.CreatedOn <= '2014-09-30'))

	Retrieves the # of requests that were INITIATED in each month of the fiscal year, for each data source.
*/
Insert into #rslt(DataSource,FiscalYear, fiscalquarter,FiscalMonth,MonthName, Initiated, Approved, ParticipantsRealSSN, ParticipantsScrambledSSN, ParticipantsPHI, Participants, Amendment)
select ds.Name as DataSource,d.FiscalYear, d.fiscalquarter, d.fiscalMonth,d.MonthName, Count(r.ID), 0,0,0,0,0, r.amendment
from hib.Request r
	inner join hib.researchstudydatasource rsds on r.ID = rsds.ResearchStudyID
	inner join hib.Datasource ds on rsds.DataSourceID = ds.ID
	inner join hib.Event e on (r.ID = e.RequestID 
												and e.Description = 'Initiated Data Access Request' 
												--and initial = 1	--no longer using initial as part of the initiated event
												/*Change per Fiscal year*/
												and (cast(e.CreatedOn as date) >= '2013-10-01' and cast(e.CreatedOn as date) <= '2014-09-30'))
	inner join dm.Date d on cast(Convert(varchar(10),e.CreatedOn,112) as int) = d.dateSID
--where r.requestType=2	--Research request
group by ds.Name,d.FiscalYear, d.fiscalquarter,d.fiscalMonth, d.MonthName,r.amendment
order by 1,2,3,4,5,6


/*
	Retrieves the # of requests that were APPROVED in each month of the fiscal year, for each data source.
*/
--Insert into #rslt(DataSource,FiscalYear, fiscalquarter, FiscalMonth,MonthName, Initiated, Approved, Participants, Amendment)
Insert into #rslt(DataSource,FiscalYear, fiscalquarter,FiscalMonth,MonthName, Initiated, Approved, ParticipantsRealSSN, ParticipantsScrambledSSN, ParticipantsPHI, Participants, Amendment)
select ds.Name as DataSource,d.FiscalYear, d.fiscalQuarter, d.fiscalMonth,d.MonthName, 0,Count(distinct r.ID), 0,0,0, Count(distinct p.ID),r.amendment	--keep only the distinct requestIDs (dm.ReviewAggregate can contain multiple review events for each request)
from dm.ReviewAggregate ra
	inner join hib.Request r on ra.RequestID= r.ID

	inner join hib.participant p on (p.requestid=r.ID and p.dataaccess=1)	--participants with data access

	inner join hib.researchstudydatasource rsds on r.ID = rsds.ResearchStudyID
	inner join hib.Datasource ds on rsds.DataSourceID = ds.ID
	inner join dm.ReviewAggregate ra2 on (ra.RequestID = ra2.RequestID	--TODO: might want to just use the hib.event table instead of dm.ReviewAggregate here
				 and ra2.ReviewType = 'Final NDS' 
				 and ra2.CurrentStatus = 'Approved' 
				 /*Change per your reporting FY*/
				 and (cast(ra2.EndDate as date) >= '2013-10-01' and cast(ra2.EndDate as date) <= '2014-09-30'))
	inner join dm.Date d on cast(Convert(varchar(10),ra2.EndDate,112) as int) = d.dateSID
--where r.requestType=2	--Research request
group by ds.Name,d.FiscalYear, d.fiscalquarter,d.fiscalMonth, d.MonthName,r.amendment
order by 1,2,3,4,5,6


/* Get the identifier specific details for the # of participants approved for the data sources */
/*
	For each month of the fiscal year, for each data source:
	Retrieves the # of participants that were APPROVED for Real SSN
*/
Insert into #rslt(DataSource,FiscalYear, fiscalquarter,FiscalMonth,MonthName, Initiated, Approved, ParticipantsRealSSN, ParticipantsScrambledSSN, ParticipantsPHI, Participants, Amendment)
select ds.Name as DataSource,d.FiscalYear, d.fiscalQuarter, d.fiscalMonth,d.MonthName, 0,0, Count(distinct p.ID),0,0,0, r.amendment	--keep only the distinct requestIDs (dm.ReviewAggregate can contain multiple review events for each request)
from dm.ReviewAggregate ra
	inner join hib.Request r on (ra.RequestID= r.ID and r.realssnflag=1)	--requests with Real SSN selected


	inner join hib.participant p on (p.requestid=r.ID and p.dataaccess=1)	--participants with data access

	inner join hib.researchstudydatasource rsds on r.ID = rsds.ResearchStudyID
	inner join hib.Datasource ds on rsds.DataSourceID = ds.ID
	inner join dm.ReviewAggregate ra2 on (ra.RequestID = ra2.RequestID	--TODO: might want to just use the hib.event table instead of dm.ReviewAggregate here
				 and ra2.ReviewType = 'Final NDS' 
				 and ra2.CurrentStatus = 'Approved' 
				 /*Change per your reporting FY*/
				 and (cast(ra2.EndDate as date) >= '2013-10-01' and cast(ra2.EndDate as date) <= '2014-09-30'))
	inner join dm.Date d on cast(Convert(varchar(10),ra2.EndDate,112) as int) = d.dateSID
--where r.requestType=2	--Research request
group by ds.Name,d.FiscalYear, d.fiscalquarter,d.fiscalMonth, d.MonthName,r.amendment
order by 1,2,3,4,5,6

/*
	For each month of the fiscal year, for each data source:
	Retrieves the # of participants that were APPROVED for Scrambled SSN	
*/
Insert into #rslt(DataSource,FiscalYear, fiscalquarter,FiscalMonth,MonthName, Initiated, Approved, ParticipantsRealSSN, ParticipantsScrambledSSN, ParticipantsPHI, Participants, Amendment)
select ds.Name as DataSource,d.FiscalYear, d.fiscalQuarter, d.fiscalMonth,d.MonthName, 0,0, 0,Count(distinct p.ID),0,0, r.amendment	--keep only the distinct requestIDs (dm.ReviewAggregate can contain multiple review events for each request)
from dm.ReviewAggregate ra
	inner join hib.Request r on (ra.RequestID= r.ID and r.scrambledssnflag=1)	--requests with Scrambled SSN selected


	inner join hib.participant p on (p.requestid=r.ID and p.dataaccess=1)	--participants with data access

	inner join hib.researchstudydatasource rsds on r.ID = rsds.ResearchStudyID
	inner join hib.Datasource ds on rsds.DataSourceID = ds.ID
	inner join dm.ReviewAggregate ra2 on (ra.RequestID = ra2.RequestID	--TODO: might want to just use the hib.event table instead of dm.ReviewAggregate here
				 and ra2.ReviewType = 'Final NDS' 
				 and ra2.CurrentStatus = 'Approved' 
				 /*Change per your reporting FY*/
				 and (cast(ra2.EndDate as date) >= '2013-10-01' and cast(ra2.EndDate as date) <= '2014-09-30'))
	inner join dm.Date d on cast(Convert(varchar(10),ra2.EndDate,112) as int) = d.dateSID
--where r.requestType=2	--Research request
group by ds.Name,d.FiscalYear, d.fiscalquarter,d.fiscalMonth, d.MonthName,r.amendment
order by 1,2,3,4,5,6

/*
	For each month of the fiscal year, for each data source:
	Retrieves the # of participants that were APPROVED for "Identifiable data but no real or scrambled SSNs"	
*/
Insert into #rslt(DataSource,FiscalYear, fiscalquarter,FiscalMonth,MonthName, Initiated, Approved, ParticipantsRealSSN, ParticipantsScrambledSSN, ParticipantsPHI, Participants, Amendment)
select ds.Name as DataSource,d.FiscalYear, d.fiscalQuarter, d.fiscalMonth,d.MonthName, 0,0, 0,0,Count(distinct p.ID),0, r.amendment	--keep only the distinct requestIDs (dm.ReviewAggregate can contain multiple review events for each request)
from dm.ReviewAggregate ra
	inner join hib.Request r on (ra.RequestID= r.ID and r.phidataflag=1)	--requests with "Identifiable data but no real or scrambled SSNs" selected


	inner join hib.participant p on (p.requestid=r.ID and p.dataaccess=1)	--participants with data access

	inner join hib.researchstudydatasource rsds on r.ID = rsds.ResearchStudyID
	inner join hib.Datasource ds on rsds.DataSourceID = ds.ID
	inner join dm.ReviewAggregate ra2 on (ra.RequestID = ra2.RequestID	--TODO: might want to just use the hib.event table instead of dm.ReviewAggregate here
				 and ra2.ReviewType = 'Final NDS' 
				 and ra2.CurrentStatus = 'Approved' 
				 /*Change per your reporting FY*/
				 and (cast(ra2.EndDate as date) >= '2013-10-01' and cast(ra2.EndDate as date) <= '2014-09-30'))
	inner join dm.Date d on cast(Convert(varchar(10),ra2.EndDate,112) as int) = d.dateSID
--where r.requestType=2	--Research request
group by ds.Name,d.FiscalYear, d.fiscalquarter,d.fiscalMonth, d.MonthName,r.amendment
order by 1,2,3,4,5,6
/* end identifier-specific calculations */


/*
	Step 3. Create the total columns for each Data Source in each Fiscal Month

	ParticipantsRealSSN int,
	ParticipantsScrambledSSN int,
	ParticipantsPHI int,
*/
--insert into #rsltTotal(DataSource, FiscalYear, Fiscalquarter, MonthName, InitiatedNew, InitiatedAmend, Initiated, ApprovedNew, ApprovedAmend, Approved, ParticipantsNew, ParticipantsAmend, Participants)
insert into #rsltTotal(DataSource, FiscalYear, Fiscalquarter, MonthName, InitiatedNew, InitiatedAmend, Initiated, ApprovedNew, ApprovedAmend, Approved, ParticipantsNew,ParticipantsAmend,Participants, ParticipantsRealNew,ParticipantsRealAmend,ParticipantsReal, ParticipantsScrambledNew,ParticipantsScrambledAmend,ParticipantsScrambled, ParticipantsPHINew,ParticipantsPHIAmend,ParticipantsPHI)
select DataSource, FiscalYear, Fiscalquarter, MonthName, Sum(initiated),0,Sum(initiated), Sum(Approved),0,Sum(Approved), Sum(Participants),0,Sum(Participants), Sum(ParticipantsRealSSN),0,Sum(ParticipantsRealSSN), Sum(ParticipantsScrambledSSN),0,Sum(ParticipantsScrambledSSN), Sum(ParticipantsPHI),0,Sum(ParticipantsPHI)
from #rslt
where amendment=0	--new request
group by DataSource, FiscalYEar, fiscalquarter, FiscalMonth, MonthName
order by  DataSource, FiscalYEar, fiscalquarter,FiscalMonth, MonthName

--insert into #rsltTotal(DataSource, FiscalYear, Fiscalquarter, MonthName, InitiatedNew, InitiatedAmend, Initiated, ApprovedNew, ApprovedAmend, Approved, ParticipantsNew, ParticipantsAmend, Participants)
insert into #rsltTotal(DataSource, FiscalYear, Fiscalquarter, MonthName, InitiatedNew, InitiatedAmend, Initiated, ApprovedNew, ApprovedAmend, Approved, ParticipantsNew,ParticipantsAmend,Participants, ParticipantsRealNew,ParticipantsRealAmend,ParticipantsReal, ParticipantsScrambledNew,ParticipantsScrambledAmend,ParticipantsScrambled, ParticipantsPHINew,ParticipantsPHIAmend,ParticipantsPHI)
select DataSource, FiscalYear, Fiscalquarter, MonthName, 0,Sum(initiated),Sum(initiated), 0,Sum(Approved),Sum(Approved), 0,Sum(Participants),Sum(Participants), 0,Sum(ParticipantsRealSSN),Sum(ParticipantsRealSSN), 0,Sum(ParticipantsScrambledSSN),Sum(ParticipantsScrambledSSN), 0,Sum(ParticipantsPHI),Sum(ParticipantsPHI)
from #rslt
where amendment=1	--amendment
group by DataSource, FiscalYEar, fiscalquarter, FiscalMonth, MonthName
order by  DataSource, FiscalYEar, fiscalquarter,FiscalMonth, MonthName


/*Select the final output (with the total columns)*/
select DataSource, FiscalYear, Fiscalquarter, MonthName as FYMonth, 
		Sum(InitiatedNew) as InitiatedinFMNew, Sum(InitiatedAmend) as InitiatedinFMAmend, Sum(Initiated) as InitiatedinFM, 
		Sum(ApprovedNew) as ApprovedinFMNew, Sum(ApprovedAmend) as ApprovedinFMAmend, Sum(Approved) as ApprovedinFM, 
		Sum(ParticipantsNew) as ParticipantsApprovedinFMNew, Sum(ParticipantsAmend) as ParticipantsApprovedinFMAmend, Sum(Participants) as ParticipantsApprovedinFM,

		Sum(ParticipantsRealNew) as ParticipantsRealApprovedinFMNew, Sum(ParticipantsRealAmend) as ParticipantsRealApprovedinFMAmend, Sum(ParticipantsReal) as ParticipantsRealApprovedinFM,
		Sum(ParticipantsScrambledNew) as ParticipantsScrambledApprovedinFMNew, Sum(ParticipantsScrambledAmend) as ParticipantsScrambledApprovedinFMAmend, Sum(ParticipantsScrambled) as ParticipantsScrambledApprovedinFM,
		Sum(ParticipantsPHINew) as ParticipantsPHIApprovedinFMNew, Sum(ParticipantsPHIAmend) as ParticipantsPHIApprovedinFMAmend, Sum(ParticipantsPHI) as ParticipantsPHIApprovedinFM

from #rsltTotal
group by DataSource, FiscalYEar, fiscalquarter, FiscalMonth, MonthName
order by  DataSource, FiscalYEar, fiscalquarter,FiscalMonth, MonthName

