–Create Temp Table to Store Results in
DECLARE @results AS TABLE
(
[Month] datetime not null
,[Median] int not null
);
–This variable will determine the date
DECLARE @IntDate as int
set @IntDate = WHILE (@IntDate <<> 0)
BEGIN
–Create Temp Table
DECLARE @table AS TABLE
(
[Rank] int not null
,[Days Open] int not null
);
–Insert records into Temp Table
insert into @table
SELECT rank() OVER (ORDER BY DATEADD(mm, DATEDIFF(mm, 0, DATEADD(ss, SVR.close_date, ‘1970’)), 0), DATEDIFF(day,DATEADD(ss, SVR.open_date, ‘1970’),DATEADD(ss, SVR.close_date, ‘1970’)),[SVR].[ref_num]) as [Rank]
,DATEDIFF(day,DATEADD(ss, SVR.open_date, ‘1970’),DATEADD(ss, SVR.close_date, ‘1970’)) as [Days Open]
FROM
mdbrpt.dbo.View_Request SVR
LEFT OUTER JOIN dbo.dtv_apps_systems vapp
on SVR.category = vapp.persid
LEFT OUTER JOIN dbo.prob_ctg pctg
on SVR.category = pctg.persid
Left Outer Join [mdbrpt].[dbo].[rootcause] as [Root Cause]
on [SVR].[rootcause]=[Root Cause].[id]
Left Outer Join [mdbrpt].[dbo].[cr_stat] as [Status]
on [SVR].[status]=[Status].[code]
LEFT OUTER JOIN [mdbrpt].[dbo].[net_res] as [net]
on [net].[id]=SVR.[affected_rc]
WHERE
SVR.Type IN (‘P’)
AND
SVR.close_date IS NOT NULL
AND
[Status].[SYM] = ‘Closed’
AND
SVR.parent is null
AND
[Root Cause].[sym] in ( ‘RC – Application’,’RC – Hardware’, ‘RC – Operational’, ‘RC – Unknown’)
AND
(
[vapp].[appl_name] in (‘3PI’,’Billing Rpts/Files’,’Collabrent’,’Reports’,’STMS’,’STMS 2′,’Telco’,’Comergent’,’OOM’,’C3-BAU’,’C3-DD’,’DIRECTV’,’DIRECTV Sales’,’DIRECTV Self Care’,’Dealer Website’,’EI Servlet’,’Enterprise Integration’,’ET’,’ICAN’,’ODS’,’SB-SCM’,’SeeBeyond’,’Digital Dashboard’,’IVR’,’OMS’,’Order Services’,’Retail Services’,’OSCAR’,’SAP’,’CTI’,’RIO’,’RIO Call Center’,’RIO Field Services’,’FSS-RIO3′,’TAOS’,’TCS’)
OR
pctg.sym in (‘Systems.Release Health Dashboard.Problem’,’DTV QA Test.Enterprise Release.Deferred Defect Log’)
AND
[Net].[nr_desc] in (‘3PI’,’Billing Rpts/Files’,’Collabrent’,’Reports’,’STMS’,’STMS 2′,’Telco’,’Comergent’,’OOM’,’C3-BAU’,’C3-DD’,’DIRECTV’,’DIRECTV Sales’,’DIRECTV Self Care’,’Dealer Website’,’EI Servlet’,’Enterprise Integration’,’ET’,’ICAN’,’ODS’,’SB-SCM’,’SeeBeyond’,’Digital Dashboard’,’IVR’,’OMS’,’Order Services’,’Retail Services’,’OSCAR’,’SAP’,’CTI’,’RIO’,’RIO Call Center’,’RIO Field Services’,’FSS-RIO3′,’TAOS’,’TCS’)
)
AND
DATEADD(mm, DATEDIFF(mm, 0, DATEADD(ss, SVR.close_date, ‘1970’)), 0) = DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,@IntDate,getdate())), 0)
ORDER BY [Days Open]
DECLARE @Count AS INT
SELECT @Count = COUNT(*) FROM @table;
WITH MyResults(RowNo, [Days Open]) AS
(
SELECT RowNo, [Days Open] FROM (SELECT ROW_NUMBER() OVER (ORDER BY [Days Open]) AS RowNo, [Days Open] FROM @table) AS Foo
)
insert into @results
SELECT
DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,@IntDate,getdate())), 0) as [Month]
,AVG([Days Open])as [Median] FROM MyResults WHERE RowNo = (@Count+1)/2 OR RowNo = ((@Count+1)%2) * ((@Count+2)/2)
set @IntDate = @IntDate+1
DELETE FROM @table
END
select *
from @results
order by [Month]
