USE $(DATA_BASE_NAME);
GO

/****** Object:  StoredProcedure [WS].[GetMedianWaittimes]    Script Date: 4/11/2016 12:52:42 PM ******/
DROP PROCEDURE [WS].[GetMedianWaittimes]
GO

/****** Object:  StoredProcedure [WS].[GetMedianWaittimes]    Script Date: 4/11/2016 12:52:42 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [WS].[GetMedianWaittimes]
@requestType varchar(25)
as

declare @RequestTypeID int 
select @requesttypeID = ID from hib.requestType
where Name = @RequestType 

if (select object_ID('tempdb..#evnt') ) < 0 
drop table #evnt


if (select object_ID('tempdb..#MWT') ) < 0 
drop table #MWT

Create table #MWT 
(
    SortOrder Int not null,
    Metric varchar(30) not null,
    MedianWaitTime numeric(10,3)
)

SELECT 
      e.[requestid]

      /*Initiate*/
      --commenting out the time period of initiated to submitted (no one is waiting on the the requestor before the request is submitted)
      --, Case when et.Name = 'Initiated' then e.CreatedOn end as RequestorInitiate
      --, Case when et.Name = 'Submitted' then e.CreatedOn end as RequestorEndInitiate
      
       /*Init NDS Review*/
      , Case when et.Name = 'Request Sent for Review' and g.Name = 'DART NDS Administrators' and Initial = 1  then e.CreatedOn end as InitNDSReviewStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Sent for Review' and g.Name = 'DART NDS Administrators' and Initial = 1  then e.CreatedOn end) as InitNDSReviewStartRank
      
      , Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed') and g.Name = 'DART NDS Administrators'   and Initial = 1  then e.CreatedOn end as InitNDSReviewEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed') and g.Name = 'DART NDS Administrators'   and Initial = 1  then e.CreatedOn end) as InitNDSReviewEndRank
      
      , Case when et.Name = 'Change Requested' and g.Name = 'DART NDS Administrators'  and Initial = 1 then e.CreatedOn end as InitNDSReviewerChangeStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Change Requested' and g.Name = 'DART NDS Administrators'  and Initial = 1 then e.CreatedOn end) as InitNDSChangeStartRankr
      
      , Case when et.Name = 'Request Submitted with Changes' and g.Name = 'DART NDS Administrators'   and Initial = 1 then e.CreatedOn end as InitNDSReviewerChangeEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Submitted with Changes' and g.Name = 'DART NDS Administrators'   and Initial = 1 then e.CreatedOn end) as InitNDSChangeEndRankr
      
       /*Privacy Review*/
      , Case when et.Name = 'Request Sent for Review' and g.Name = 'DART Privacy Administrators' then e.CreatedOn end as PrivacyReviewStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Sent for Review' and g.Name = 'DART Privacy Administrators' then e.CreatedOn end) as PrivacyReviewStartRank
      
      , Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed', 'Review Withdrawn') and g.Name = 'DART Privacy Administrators' then e.CreatedOn end as PrivacyReviewEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed', 'Review Withdrawn') and g.Name = 'DART Privacy Administrators' then e.CreatedOn end) as PrivacyReviewEndRank
      
      , Case when et.Name = 'Change Requested' and g.Name = 'DART Privacy Administrators' then e.CreatedOn end as PrivacyRequestorChangeStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Change Requested' and g.Name = 'DART Privacy Administrators' then e.CreatedOn end) as PrivacyRequestorChangeStartRank
      
      , Case when et.Name = 'Request Submitted with Changes' and g.Name = 'DART Privacy Administrators' then e.CreatedOn end as PrivacyRequestorChangeEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Submitted with Changes' and g.Name = 'DART Privacy Administrators' then e.CreatedOn end) as PrivacyRequestorChangeEndRank

       /*Security Review*/
      , Case when et.Name = 'Request Sent for Review' and g.Name = 'DART Security Administrators' then e.CreatedOn end as SecurityReviewStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Sent for Review' and g.Name = 'DART Security Administrators' then e.CreatedOn end) as SecurityReviewStartRank

      , Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed', 'Review Withdrawn') and g.Name = 'DART Security Administrators' then e.CreatedOn end as SecurityReviewEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed', 'Review Withdrawn') and g.Name = 'DART Security Administrators' then e.CreatedOn end) as SecurityReviewEndRank

      , Case when et.Name = 'Change Requested' and g.Name = 'DART Security Administrators' then e.CreatedOn end as SecurityRequestorChangeStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Change Requested' and g.Name = 'DART Security Administrators' then e.CreatedOn end) as SecurityRequestorChangeStartRank

      , Case when et.Name = 'Request Submitted with Changes' and g.Name = 'DART Security Administrators' then e.CreatedOn end as SecurityRequestorChangeEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Submitted with Changes' and g.Name = 'DART Security Administrators' then e.CreatedOn end) as SecurityRequestorChangeEndRank

        /*ORD Review*/
      , Case when et.Name = 'Request Sent for Review' and g.Name = 'DART ORD Administrators' then e.CreatedOn end as ORDReviewStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Sent for Review' and g.Name = 'DART ORD Administrators' then e.CreatedOn end) as ORDReviewStartRank

      , Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed', 'Review Withdrawn') and g.Name = 'DART ORD Administrators' then e.CreatedOn end as ORDReviewEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed', 'Review Withdrawn') and g.Name = 'DART ORD Administrators' then e.CreatedOn end) as ORDReviewEndRank

      , Case when et.Name = 'Change Requested' and g.Name = 'DART ORD Administrators' then e.CreatedOn end as ORDRequestorChangeStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Change Requested' and g.Name = 'DART ORD Administrators' then e.CreatedOn end) as ORDRequestorChangeStartRank

      , Case when et.Name = 'Request Submitted with Changes' and g.Name = 'DART ORD Administrators' then e.CreatedOn end as ORDRequestorChangeEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Submitted with Changes' and g.Name = 'DART ORD Administrators' then e.CreatedOn end) as ORDRequestorChangeEndRank


       /* Capri */
      , Case when et.Name = 'Request Sent for Review' and g.id=13 then e.CreatedOn end as CapriReviewStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Sent for Review' and g.id=13 then e.CreatedOn end) as CapriReviewStartRank

      , Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed', 'Review Withdrawn') and g.id=13 then e.CreatedOn end as CapriReviewEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed', 'Review Withdrawn') and g.id=13 then e.CreatedOn end) as CapriReviewEndRank

      , Case when et.Name = 'Change Requested' and g.id=13 then e.CreatedOn end as CapriRequestorChangeStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Change Requested' and g.id=13 then e.CreatedOn end) as CapriRequestorChangeStartRank

      , Case when et.Name = 'Request Submitted with Changes' and g.id=13 then e.CreatedOn end as CapriRequestorChangeEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Submitted with Changes' and g.id=13 then e.CreatedOn end) as CapriRequestorChangeEndRank


       /* Homeless Registry */
      , Case when et.Name = 'Request Sent for Review' and g.id=16 then e.CreatedOn end as HomelessReviewStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Sent for Review' and g.id=16 then e.CreatedOn end) as HomelessReviewStartRank

      , Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed', 'Review Withdrawn') and g.id=16 then e.CreatedOn end as HomelessReviewEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed', 'Review Withdrawn') and g.id=16 then e.CreatedOn end) as HomelessReviewEndRank

      , Case when et.Name = 'Change Requested' and g.id=16 then e.CreatedOn end as HomelessRequestorChangeStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Change Requested' and g.id=16 then e.CreatedOn end) as HomelessRequestorChangeStartRank

      , Case when et.Name = 'Request Submitted with Changes' and g.id=16 then e.CreatedOn end as HomelessRequestorChangeEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Submitted with Changes' and g.id=16 then e.CreatedOn end) as HomelessRequestorChangeEndRank


       /* OEF/OIF */
      , Case when et.Name = 'Request Sent for Review' and g.id=9 then e.CreatedOn end as OEFOIFReviewStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Sent for Review' and g.id=9 then e.CreatedOn end) as OEFOIFReviewStartRank

      , Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed', 'Review Withdrawn') and g.id=9 then e.CreatedOn end as OEFOIFReviewEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed', 'Review Withdrawn') and g.id=9 then e.CreatedOn end) as OEFOIFReviewEndRank

      , Case when et.Name = 'Change Requested' and g.id=9 then e.CreatedOn end as OEFOIFRequestorChangeStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Change Requested' and g.id=9 then e.CreatedOn end) as OEFOIFRequestorChangeStartRank

      , Case when et.Name = 'Request Submitted with Changes' and g.id=9 then e.CreatedOn end as OEFOIFRequestorChangeEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Submitted with Changes' and g.id=9 then e.CreatedOn end) as OEFOIFRequestorChangeEndRank


       /* SQDUG */
      , Case when et.Name = 'Request Sent for Review' and g.id=11 then e.CreatedOn end as SQDUGReviewStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Sent for Review' and g.id=11 then e.CreatedOn end) as SQDUGReviewStartRank

      , Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed', 'Review Withdrawn') and g.id=11 then e.CreatedOn end as SQDUGReviewEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed', 'Review Withdrawn') and g.id=11 then e.CreatedOn end) as SQDUGReviewEndRank

      , Case when et.Name = 'Change Requested' and g.id=11 then e.CreatedOn end as SQDUGRequestorChangeStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Change Requested' and g.id=11 then e.CreatedOn end) as SQDUGRequestorChangeStartRank

      , Case when et.Name = 'Request Submitted with Changes' and g.id=11 then e.CreatedOn end as SQDUGRequestorChangeEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Submitted with Changes' and g.id=11 then e.CreatedOn end) as SQDUGRequestorChangeEndRank


       /*Final NDS Review*/
      , Case when et.Name = 'Request Sent for Review' and g.Name = 'DART NDS Administrators' and Initial = 0  then e.CreatedOn end as FinalNDSReviewStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Sent for Review' and g.Name = 'DART NDS Administrators' and Initial = 0  then e.CreatedOn end) as FinalNDSReviewStartRank
      
      , Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed') and g.Name = 'DART NDS Administrators'   and Initial = 0  then e.CreatedOn end as FinalNDSReviewEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name in ('Review Denied','Review Approval','Change Requested', 'Closed' ) and g.Name = 'DART NDS Administrators'   and Initial = 0  then e.CreatedOn end) as FinalNDSReviewEndRank
      
      , Case when et.Name = 'Change Requested' and g.Name = 'DART NDS Administrators'  and Initial = 0 then e.CreatedOn end  as FinalNDSRequestorChangeStart
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Change Requested' and g.Name = 'DART NDS Administrators'  and Initial = 0 then e.CreatedOn end) as FinalNDSRequestorChangeStartRank
      
      , Case when et.Name = 'Request Submitted with Changes' and g.Name = 'DART NDS Administrators'   and Initial = 0 then e.CreatedOn end as FinalNDSRequestorChangeEnd
      , dense_rank() over (partition by E.RequestID order by Case when et.Name = 'Request Submitted with Changes' and g.Name = 'DART NDS Administrators'   and Initial = 0 then e.CreatedOn end) as FinalNDSRequestorChangeEndRank

    into #Evnt
  FROM [hib].[event] e
    inner join hib.EventType et on e.EventTypeID = et.ID
    inner join hib.EventGroup eg on e.ID = eg.EventID
    inner join hib.[Group] G on eg.GrouPID = G.ID
    inner join hib.Request req on e.RequestID = req.ID
    where req.RequestType =  @requestTypeID
    order by e.RequestID, e.CreatedOn asc

    
/*Init*/
;with c1 as (
            --commenting out the time period of initiated to submitted (no one is waiting on the the requestor before the request is submitted)
            --select 
            --          RequestID
            --          , 'Initiate Request' as Metric
            --          , 1 as rankr
            --          , DateDiff(minute, max(Coalesce(RequestorInitiate,'1900-01-01')), min(Coalesce(RequestorEndInitiate,getdate()))) / 60.0 as HoursSpent
            --          from #evnt
            --          group by RequestID having max(Coalesce(RequestorInitiate,'1900-01-01')) > Dateadd(month, -3, getdate())
            --
            --  union all 

                                select 
                                e1.RequestID
                                , 'Initiate Request' as Metric
                                , e1.InitNDSReviewStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.InitNDSReviewStart,'1900-01-01')), min(Coalesce(e2.InitNDSReviewEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.InitNDSReviewStartRank = e2.InitNDSReviewEndRank
                                group by e1.RequestID, e1.InitNDSReviewStartRank having max(Coalesce(e1.InitNDSReviewStart,'1900-01-01')) > Dateadd(month, -3, getdate())

                union all 
                                select 
                                e1.RequestID
                                , 'NDS change Review' as Metric
                                , e1.InitNDSChangeStartRankr as Rankr
                                , DateDiff(minute, max(Coalesce(e1.InitNDSReviewerChangeStart,'1900-01-01')), min(Coalesce(e2.InitNDSReviewerChangeEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.InitNDSChangeStartRankr = e2.InitNDSChangeEndRankr
                                group by e1.RequestID, e1.InitNDSChangeStartRankr having max(Coalesce(e1.InitNDSReviewerChangeStart,'1900-01-01')) > Dateadd(month, -3, getdate())
        )


Insert into #MWT(SortOrder, Metric, MedianWaitTime)
select 1 as Srt, Metric, avg(hs) as medianWaitTime
from (select RequestID, Metric, Sum(HoursSpent) hs from c1 group by RequestID, Metric ) c2 
group by Metric

/*Privacy*/
;with c2 as (       select 
                                e1.RequestID
                                , 'Privacy Review' as Metric
                                , e1.PrivacyReviewStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.PrivacyReviewStart,'1900-01-01')), min(Coalesce(e2.PrivacyReviewEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.PrivacyReviewStartRank = e2.PrivacyReviewEndRank
                                group by e1.RequestID, e1.PrivacyReviewStartRank having max(Coalesce(e1.PrivacyReviewStart,'1900-01-01')) > Dateadd(month, -3, getdate())

                union all 
                                select 
                                e1.RequestID
                                , 'Privacy Requestor Change' as Metric
                                , e1.PrivacyRequestorChangeStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.PrivacyRequestorChangeStart,'1900-01-01')), min(Coalesce(e2.PrivacyRequestorChangeEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.PrivacyRequestorChangeStartRank = e2.PrivacyRequestorChangeEndRank
                                group by e1.RequestID, e1.PrivacyRequestorChangeStartRank having max(Coalesce(e1.PrivacyRequestorChangeStart,'1900-01-01')) > Dateadd(month, -3, getdate())
        )


Insert into #MWT(SortOrder, Metric, MedianWaitTime)
select 2 as Srt, Metric, avg(hs) as medianWaitTime
from (select RequestID, Metric, Sum(HoursSpent) hs from c2 group by RequestID, Metric ) c2 
group by Metric


/*Security*/
;with c3 as (       select 
                                e1.RequestID
                                , 'Security Review' as Metric
                                , e1.SecurityReviewStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.SecurityReviewStart,'1900-01-01')), min(Coalesce(e2.SecurityReviewEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.SecurityReviewStartRank = e2.SecurityReviewEndRank
                                group by e1.RequestID, e1.SecurityReviewStartRank having max(Coalesce(e1.SecurityReviewStart,'1900-01-01')) > Dateadd(month, -3, getdate())

                union all 
                                select 
                                e1.RequestID
                                , 'Security Requestor Change' as Metric
                                , e1.SecurityRequestorChangeStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.SecurityRequestorChangeStart,'1900-01-01')), min(Coalesce(e2.SecurityRequestorChangeEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.SecurityRequestorChangeStartRank = e2.SecurityRequestorChangeEndRank
                                group by e1.RequestID, e1.SecurityRequestorChangeStartRank having max(Coalesce(e1.SecurityRequestorChangeStart,'1900-01-01')) > Dateadd(month, -3, getdate())
        )


Insert into #MWT(SortOrder, Metric, MedianWaitTime)
select 3 as Srt, Metric, avg(hs) as medianWaitTime
from (select RequestID, Metric, Sum(HoursSpent) hs from c3 group by RequestID, Metric ) c3
group by Metric



/*ORD*/
;with c4 as (       select 
                                e1.RequestID
                                , 'ORD Review' as Metric
                                , e1.ORDReviewStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.ORDReviewStart,'1900-01-01')), min(Coalesce(e2.ORDReviewEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.ORDReviewStartRank = e2.ORDReviewEndRank
                                group by e1.RequestID, e1.ORDReviewStartRank having max(Coalesce(e1.ORDReviewStart,'1900-01-01')) > Dateadd(month, -3, getdate())

                union all 
                                select 
                                e1.RequestID
                                , 'ORD Requestor Change' as Metric
                                , e1.ORDRequestorChangeStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.ORDRequestorChangeStart,'1900-01-01')), min(Coalesce(e2.ORDRequestorChangeEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.ORDRequestorChangeStartRank = e2.ORDRequestorChangeEndRank
                                group by e1.RequestID, e1.ORDRequestorChangeStartRank having max(Coalesce(e1.ORDRequestorChangeStart,'1900-01-01')) > Dateadd(month, -3, getdate())
        )

Insert into #MWT(SortOrder, Metric, MedianWaitTime)
select 4 as Srt, Metric, avg(hs) as medianWaitTime
from (select RequestID, Metric, Sum(HoursSpent) hs from c4 group by RequestID, Metric ) c4
group by Metric



/* Capri */
;with c4 as (       select 
                                e1.RequestID
                                , 'Capri Review' as Metric
                                , e1.CapriReviewStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.CapriReviewStart,'1900-01-01')), min(Coalesce(e2.CapriReviewEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.CapriReviewStartRank = e2.CapriReviewEndRank
                                group by e1.RequestID, e1.CapriReviewStartRank having max(Coalesce(e1.CapriReviewStart,'1900-01-01')) > Dateadd(month, -3, getdate())

                union all 
                                select 
                                e1.RequestID
                                , 'Capri Requestor Change' as Metric
                                , e1.CapriRequestorChangeStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.CapriRequestorChangeStart,'1900-01-01')), min(Coalesce(e2.CapriRequestorChangeEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.CapriRequestorChangeStartRank = e2.CapriRequestorChangeEndRank
                                group by e1.RequestID, e1.CapriRequestorChangeStartRank having max(Coalesce(e1.CapriRequestorChangeStart,'1900-01-01')) > Dateadd(month, -3, getdate())
        )

Insert into #MWT(SortOrder, Metric, MedianWaitTime)
select 4 as Srt, Metric, avg(hs) as medianWaitTime
from (select RequestID, Metric, Sum(HoursSpent) hs from c4 group by RequestID, Metric ) c4
group by Metric



/* Homeless Registry */
;with c4 as (       select 
                                e1.RequestID
                                , 'Homeless Review' as Metric
                                , e1.HomelessReviewStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.HomelessReviewStart,'1900-01-01')), min(Coalesce(e2.HomelessReviewEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.HomelessReviewStartRank = e2.HomelessReviewEndRank
                                group by e1.RequestID, e1.HomelessReviewStartRank having max(Coalesce(e1.HomelessReviewStart,'1900-01-01')) > Dateadd(month, -3, getdate())

                union all 
                                select 
                                e1.RequestID
                                , 'Homeless Requestor Change' as Metric
                                , e1.HomelessRequestorChangeStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.HomelessRequestorChangeStart,'1900-01-01')), min(Coalesce(e2.HomelessRequestorChangeEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.HomelessRequestorChangeStartRank = e2.HomelessRequestorChangeEndRank
                                group by e1.RequestID, e1.HomelessRequestorChangeStartRank having max(Coalesce(e1.HomelessRequestorChangeStart,'1900-01-01')) > Dateadd(month, -3, getdate())
        )

Insert into #MWT(SortOrder, Metric, MedianWaitTime)
select 4 as Srt, Metric, avg(hs) as medianWaitTime
from (select RequestID, Metric, Sum(HoursSpent) hs from c4 group by RequestID, Metric ) c4
group by Metric


/* OEF/OIF */
;with c4 as (       select 
                                e1.RequestID
                                , 'OEFOIF Review' as Metric
                                , e1.OEFOIFReviewStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.OEFOIFReviewStart,'1900-01-01')), min(Coalesce(e2.OEFOIFReviewEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.OEFOIFReviewStartRank = e2.OEFOIFReviewEndRank
                                group by e1.RequestID, e1.OEFOIFReviewStartRank having max(Coalesce(e1.OEFOIFReviewStart,'1900-01-01')) > Dateadd(month, -3, getdate())

                union all 
                                select 
                                e1.RequestID
                                , 'OEFOIF Requestor Change' as Metric
                                , e1.OEFOIFRequestorChangeStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.OEFOIFRequestorChangeStart,'1900-01-01')), min(Coalesce(e2.OEFOIFRequestorChangeEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.OEFOIFRequestorChangeStartRank = e2.OEFOIFRequestorChangeEndRank
                                group by e1.RequestID, e1.OEFOIFRequestorChangeStartRank having max(Coalesce(e1.OEFOIFRequestorChangeStart,'1900-01-01')) > Dateadd(month, -3, getdate())
        )

Insert into #MWT(SortOrder, Metric, MedianWaitTime)
select 4 as Srt, Metric, avg(hs) as medianWaitTime
from (select RequestID, Metric, Sum(HoursSpent) hs from c4 group by RequestID, Metric ) c4
group by Metric


/* SQDUG */
;with c4 as (       select 
                                e1.RequestID
                                , 'SQDUG Review' as Metric
                                , e1.SQDUGReviewStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.SQDUGReviewStart,'1900-01-01')), min(Coalesce(e2.SQDUGReviewEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.SQDUGReviewStartRank = e2.SQDUGReviewEndRank
                                group by e1.RequestID, e1.SQDUGReviewStartRank having max(Coalesce(e1.SQDUGReviewStart,'1900-01-01')) > Dateadd(month, -3, getdate())

                union all 
                                select 
                                e1.RequestID
                                , 'SQDUG Requestor Change' as Metric
                                , e1.SQDUGRequestorChangeStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.SQDUGRequestorChangeStart,'1900-01-01')), min(Coalesce(e2.SQDUGRequestorChangeEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.SQDUGRequestorChangeStartRank = e2.SQDUGRequestorChangeEndRank
                                group by e1.RequestID, e1.SQDUGRequestorChangeStartRank having max(Coalesce(e1.SQDUGRequestorChangeStart,'1900-01-01')) > Dateadd(month, -3, getdate())
        )

Insert into #MWT(SortOrder, Metric, MedianWaitTime)
select 4 as Srt, Metric, avg(hs) as medianWaitTime
from (select RequestID, Metric, Sum(HoursSpent) hs from c4 group by RequestID, Metric ) c4
group by Metric



/*Final NDS*/
;with c5 as (       select 
                                e1.RequestID
                                , 'Final NDS Review' as Metric
                                , e1.FinalNDSReviewStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.FinalNDSReviewStart,'1900-01-01')), min(Coalesce(e2.FinalNDSReviewEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.FinalNDSReviewStartRank = e2.FinalNDSReviewEndRank
                                group by e1.RequestID, e1.FinalNDSReviewStartRank having max(Coalesce(e1.FinalNDSReviewStart,'1900-01-01')) > Dateadd(month, -3, getdate())

                union all 
                                select 
                                e1.RequestID
                                , 'Final NDS Requestor Change' as Metric
                                , e1.FinalNDSRequestorChangeStartRank as Rankr
                                , DateDiff(minute, max(Coalesce(e1.FinalNDSRequestorChangeStart,'1900-01-01')), min(Coalesce(e2.FinalNDSRequestorChangeEnd,getdate()))) / 60.0  as HoursSpent
                                from #evnt e1 
                                    left join #evnt e2 on e1.RequestID = e2.RequestID and e1.FinalNDSRequestorChangeStartRank = e2.FinalNDSRequestorChangeEndRank
                                group by e1.RequestID, e1.FinalNDSRequestorChangeStartRank having max(Coalesce(e1.FinalNDSRequestorChangeStart,'1900-01-01')) > Dateadd(month, -3, getdate())
        )

Insert into #MWT(SortOrder, Metric, MedianWaitTime)
select 5 as Srt, Metric, avg(hs) as medianWaitTime
from (select RequestID, Metric, Sum(HoursSpent) hs from c5 group by RequestID, Metric ) c5
group by Metric

/*Final Output*/
select 'Initial NDS Review' as Metric
        , Coalesce(Max(case when Metric = 'Initiate Request' then MedianWaitTime / 24 end),0) as RequestorMedian
        , Coalesce(Max(case when Metric = 'NDS change Review' then MedianWaitTime / 24 end),0) as ReviewMedian
from #MWT
where Metric in ('Initiate Request','NDS change Review')
union
select 'Privacy' as Metric
        , Coalesce(Max(case when Metric = 'Privacy Review' then MedianWaitTime / 24 end),0) as RequestorMedian
        , Coalesce(Max(case when Metric = 'Privacy Requestor Change' then MedianWaitTime / 24 end),0) as ReviewMedian
from #MWT
where Metric in ('Privacy Review','Privacy Requestor Change')
union 
select 'Security' as Metric
        , Coalesce(Max(case when Metric = 'Security Review' then MedianWaitTime / 24 end),0) as RequestorMedian
        , Coalesce(Max(case when Metric = 'Security Requestor Change' then MedianWaitTime / 24 end),0) as ReviewMedian
from #MWT
where Metric in ('Security Review','Security Requestor Change')
union 
select 'ORD' as Metric
        , Coalesce(Max(case when Metric = 'ORD Review' then MedianWaitTime / 24 end),0) as RequestorMedian
        , Coalesce(Max(case when Metric = 'ORD Requestor Change' then MedianWaitTime / 24 end),0) as ReviewMedian
from #MWT
where Metric in ('ORD Review','ORD Requestor Change')
union 
select 'CAPRI' as Metric
        , Coalesce(Max(case when Metric = 'Capri Review' then MedianWaitTime / 24 end),0) as RequestorMedian
        , Coalesce(Max(case when Metric = 'Capri Requestor Change' then MedianWaitTime / 24 end),0) as ReviewMedian
from #MWT
where Metric in ('Capri Review','Capri Requestor Change')
union 
select 'Homeless Registry' as Metric
        , Coalesce(Max(case when Metric = 'Homeless Review' then MedianWaitTime / 24 end),0) as RequestorMedian
        , Coalesce(Max(case when Metric = 'Homeless Requestor Change' then MedianWaitTime / 24 end),0) as ReviewMedian
from #MWT
where Metric in ('Homeless Review','Homeless Requestor Change')
union 
select 'OEF-OIF' as Metric
        , Coalesce(Max(case when Metric = 'OEFOIF Review' then MedianWaitTime / 24 end),0) as RequestorMedian
        , Coalesce(Max(case when Metric = 'OEFOIF Requestor Change' then MedianWaitTime / 24 end),0) as ReviewMedian
from #MWT
where Metric in ('OEFOIF Review','OEFOIF Requestor Change')
union 
select 'SQDUG' as Metric
        , Coalesce(Max(case when Metric = 'SQDUG Review' then MedianWaitTime / 24 end),0) as RequestorMedian
        , Coalesce(Max(case when Metric = 'SQDUG Requestor Change' then MedianWaitTime / 24 end),0) as ReviewMedian
from #MWT
where Metric in ('SQDUG Review','SQDUG Requestor Change')
union 
select 'Final NDS Review' as Metric
        , Coalesce(Max(case when Metric = 'Final NDS Review' then MedianWaitTime / 24 end),0) as RequestorMedian
        , Coalesce(Max(case when Metric = 'Final NDS Requestor Change' then MedianWaitTime / 24 end),0) as ReviewMedian
from #MWT
where Metric in ('Final NDS Review','Final NDS Requestor Change')




GO
