It's been quiet on the list so I figured I'd post some queries that have helped
me find some things that my management has been looking for. My SLA names will
not match yours so modify as needed but it should get you going. I use this
query in SRS along with date parameters to generate an SLA report for the
previous 1 month (1st of month to last day of month). Execute this against
your Data Warehouse - DWDataMart db. Forgive the comments but I keep them in
my queries when I'm building them and revising them so I can do test cases for
parameters or known quantities (like a particular support group, incident
number etc). I always welcome feedback about how to do things better and
hopefully these aren't old hat for everyone.
-Casey
SELECT
CASE WHEN slaconf.DisplayName LIKE '%Priority 1%' THEN '1 - 2 hours'
WHEN slaconf.DisplayName LIKE '%Priority 2%' THEN '2 - 4 hours'
WHEN slaconf.DisplayName LIKE '%Priority 3%' THEN '3 - 8 hours'
WHEN slaconf.DisplayName LIKE '%Priority 4%' THEN '4 - 24 hours'
WHEN slaconf.DisplayName LIKE '%Priority 5%' THEN '5 - 48 hours'
WHEN slaconf.DisplayName LIKE '%within 30%' THEN 'Unassigned - 30
min'
ELSE slaconf.DisplayName
END AS 'Priority'
,Disp.DisplayName AS 'ITD Unit'
,COUNT (CASE WHEN SLAInstanceStatusValue IS NOT NULL THEN 1 END) as
[Incidents Received]
,COUNT (CASE WHEN SLAInstanceStatusValue = 'Met' THEN 1 END) as [SLA Met]
,COUNT (CASE WHEN SLAInstanceStatusValue = 'Breached' THEN 1 END) as [SLA
Breached]
,COUNT (CASE WHEN SLAInstanceStatusValue = 'Active' THEN 1 END) as [SLA
Active]
,COUNT (CASE WHEN SLAInstanceStatusValue = 'Met' or SLAInstanceStatusValue =
'Active' THEN 1 END) as [SLA Met and Active]
,CAST((COUNT (CASE WHEN SLAInstanceStatusValue = 'Met' or
SLAInstanceStatusValue = 'Active' THEN 1 END)) AS FLOAT(4))/CAST((COUNT (CASE
WHEN SLAInstanceStatusValue IS NOT NULL THEN 1 END)) AS FLOAT(4)) AS 'SLA Rate'
FROM [DWDataMart].[dbo].[SLAInstanceInformationFactvw] sla
JOIN [DWDataMart].[dbo].[WorkItemDimvw] wi on sla.WorkItemDimKey =
wi.WorkItemDimKey
JOIN [DWDataMart].[dbo].[SLAConfigurationDimvw] slaconf on
sla.SLAConfigurationDimKey = slaconf.SLAConfigurationDimKey
JOIN [DWDataMart].[dbo].[SLAInstanceStatusvw] slastatus on
sla.SLAInstanceStatusId = slastatus.SLAInstanceStatusId
JOIN [DWDataMart].[dbo].[IncidentDimvw] inc on wi.EntityDimKey =
inc.EntityDimKey
JOIN dwdatamart.dbo.IncidentTierQueuesvw tqvw on
inc.TierQueue_IncidentTierQueuesId = tqvw.IncidentTierQueuesId
JOIN [DWDataMart].[dbo].[DisplayStringDim] disp on tqvw.EnumTypeId =
disp.MPElementId
where
--wi.ID = 'IR2488'
Disp.LanguageCode = 'ENU'
--AND disp.DisplayName = 'Network'
AND (slaConf.DisplayName LIKE '%' + 'Priority' + '%' OR slaConf.DisplayName
LIKE '%within 30%')
--AND SLAInstanceStatusValue = 'Met'
AND (SLAInstanceStatusValue = 'Breached' or SLAInstanceStatusValue = 'Met' or
SLAInstanceStatusValue = 'Active' )
--AND sla.StartDate >= GetDate() - 60
AND sla.StartDate BETWEEN '09/01/2012' AND '02/28/2013'
group by slaconf.DisplayName,disp.DisplayName
order by slaconf.DisplayName
