USE $(DATA_BASE_NAME);
GO

DROP VIEW [drtrpt].[MonthlyEventStats_OEF]
GO

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

/* get the initiated, submitted, approved (by any workflow), Final NDS approved event counts for the OEF/OIF Roster File data source */
select dm.CalendarYear, dm.MonthofYear, dm.MonthName
       , sum(case when EventDescription = 'Initiated Data Access Request' then 1 else 0 end) as Initiated

--     , sum(case when EventDescription = 'Submitting Data Access Request Packet to NDS' then 1 else 0 end) as Submitted 
       , sum(case when EventType = 'Submitted' then 1 else 0 end) as Submitted 

       , sum(case when EventType = 'Request Approval' then 1 else 0 end) as 'Total_Approved_Workflows'

       , sum(case when eventdescription = 'OEF-OIF Request Approval' then 1 else 0 end) as 'OEF_Workflows_Approved'

--     , sum(case when EventDescription = 'Final NDS Approval Complete' then 1 else 0 end) as FinalNDS -- For Old Dart
       , sum(case when EventDescription = 'Final NDS Approval Complete' then 1 else 0 end) as 'NDS_Workflows_Approved' -- For Old Dart
from drtrpt.dartrequestEvent e
       inner join dm.Date dm on cast(floor(cast(e.EventDate as float)) as datetime) = dm.[Date]

       left join hib.researchstudydatasource rsds on e.requestid=rsds.researchstudyid

where  datasourceid=1033   --OEF/OIF Roster File data source
group by dm.CalendarYear, dm.MonthofYear, dm.MonthName
--order by dm.CalendarYear, dm.MonthofYear, dm.MonthName
GO