USE $(DATA_BASE_NAME);
GO

DROP VIEW [drtrpt].[MonthlyEventStats_AllWorkflows]
GO

/****** Object:  View [drtrpt].[MonthlyEventStats_AllWorkflows]    Script Date: 2/24/2016 1:58:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [drtrpt].[MonthlyEventStats_AllWorkflows]
as
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 Approved 

       , sum(case when eventdescription = 'OEF-OIF Request Approval' then 1 else 0 end) as 'OEF_Workflows_Approved'
       , sum(case when eventdescription = 'SQDUG Request Approval' then 1 else 0 end) as 'SQDUG_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]
group by dm.CalendarYear, dm.MonthofYear, dm.MonthName

GO
