USE $(DATA_BASE_NAME);
GO

DROP VIEW [drtrpt].[MonthlyEventStats_RealSSN]
GO

/****** Object:  View [drtrpt].[MonthlyEventStats_RealSSN]    Script Date: 2/24/2016 1:58:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [drtrpt].[MonthlyEventStats_RealSSN]
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 = 'Final NDS Approval Complete' then 1 else 0 end) as FinalNDS -- For Old Dart

       , sum(case when r.id is not null then 1 else 0 end) as 'Requests Approved for “Real SSN”'

from drtrpt.dartrequestEvent e
       inner join dm.Date dm on cast(floor(cast(e.EventDate as float)) as datetime) = dm.[Date]
       left join hib.request r on (e.requestid=r.id and e.EventDescription='Final NDS Approval Complete' and r.realssnflag=1) --approved for Real SSN

group by dm.CalendarYear, dm.MonthofYear, dm.MonthName



GO
