1

I have a line chart/graph that I need to include for all service outages we went through in the year. Currently, my SQL can pull data that includes any tickets creating in a ticketing system for these services.

However, if no ticket was created for a service, the service will not show in the rows or the service will show but not have a 100% Uptime for the months where no tickets were created for it.

Example of what I am looking for:

MONTH     Service1     Service2     Service3
1         100%         99.7%        100%
2         99.8%        100%         96.5%
3         100%         99.8%        100%

But what it looks like is this:

MONTH     Service1     Service2     Service3
1                      99.7%        
2         99.8%                     96.5%
3                      99.8%

The services get pulled by using a WHERE [Resource]='Affected Service' so they are dynamically brought into the table, but no data is pulled for the service if no ticket was created in that month.

Current SQL Coding:

WITH rslt (ResourceID, YearNumber, MonthNumber, AvailableMinutes, DowntimeMinutes) AS (
SELECT
    ore.ResourceID,
    DATEPART(yyyy, ipr.OpenDate_MST) YearNumber,
    DATEPART(mm, ipr.OpenDate_MST) MonthNumber,
    MAX(CASE
        WHEN DATEADD(MONTH, DATEDIFF(MONTH, -1, ipr.OpenDate_MST), -1) = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1)
        THEN (DATEPART(DD, GETDATE()) * 1440.0)
        ELSE (DATEPART(DD, DATEADD(MONTH, DATEDIFF(MONTH, -1, ipr.OpenDate_MST), -1)) * 1440.0)
 END) AvailableMinutes,
    ISNULL(SUM(DATEDIFF(mi, ipr.OutageStartTime, ipr.OutageEndTime)), 0) DowntimeMinutes

FROM
    vIncidentProblemRequest ipr
    INNER JOIN vOwnedResource ore ON ore.ResourceID = ipr.AffectedServiceID

WHERE
 CONVERT(DATETIME, CONVERT(CHAR(10), ipr.OpenDate_MST, 101)) >= '1/1/2013 12:00:00'
 AND CONVERT(DATETIME, CONVERT(CHAR(10), ipr.OpenDate_MST, 101)) <= '12/31/2013 11:59:59'
GROUP BY
 ore.ResourceID,
 DATEPART(yyyy, ipr.OpenDate_MST),
 DATEPART(mm, ipr.OpenDate_MST),
 ),

rslt2 (ResourceID, Application, ResourceClass, YearNumber, MonthNumber, AvailableMinutes, DowntimeMinutes, UptimePercent) AS (
SELECT
 ore.ResourceID,
 ore.ResourceName Application,
 ore.ResourceClass,
 rslt.YearNumber,
 rslt.MonthNumber,
 rslt.AvailableMinutes,
 ISNULL(rslt.DowntimeMinutes, 0) DowntimeMinutes,
 CASE
  WHEN rslt.DowntimeMinutes IS NULL
  THEN 1.0
  ELSE ((rslt.AvailableMinutes - rslt.DowntimeMinutes)/rslt.AvailableMinutes)
 END UptimePercent
FROM
 vOwnedResource ore
 LEFT OUTER JOIN rslt ON rslt.ResourceID = ore.ResourceID
WHERE
 ore.ResourceClass = 'Enterprise Service')

select
 MIN(DATEPART(yyyy, d.Date)) Year,
 MIN(DATEPART(mm, d.Date)) MonthNum,
 SUBSTRING(MIN(DATENAME(mm, d.Date)), 1, 3) Month,
 r.Application,
 r.ResourceClass,
 CASE
  WHEN r.UptimePercent IS NULL
  THEN 1.0
  ELSE r.UptimePercent
 END UptimePercent
FROM
 DimDate d
 INNER JOIN rslt2 r ON r.YearNumber = datepart(yyyy, d.Date) AND r.MonthNumber = datepart(mm, d.Date)
WHERE
 d.Date >= '1/1/2013 12:00:00'
 AND d.Date <= '12/31/2013 11:59:59'
GROUP BY
 datepart(yyyy, d.Date),
 datepart(mm, d.Date),
 DATENAME(mm, d.Date),
 r.Application,
 r.ResourceClass,
 r.UptimePercent
ORDER BY
 4,1,2
Dalagrath
  • 21
  • 6

3 Answers3

0

It would really help if you showed the query. However, somewhere along the way, you can solve your problem using coalesce(). Assuming the values are really numbers between 0 and 100:

select month, coalesce(service1, 100) as service1,
      coalesce(service2, 100) as service2, coalesce(service3, 100) as service3
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

How about this-

Create a temp table having all service values as 100% for each month.

After that, update the temp table with the real values based on your filter condition.

Suyash Khandwe
  • 386
  • 3
  • 11
0

The following answer describes the 'EXISTS' command. I think it will help you greatly.

Using CASE to Return a String If No Results From SELECT Statement

Community
  • 1
  • 1
mcsilvio
  • 1,098
  • 1
  • 11
  • 20
  • When using the exists statement like this in my first portion of my CTE it gives me multiple rows of data where I only need 1 row per month for the application, but am now receiving 4 or 5 rows per month per application. `CASE WHEN EXISTS ( SELECT 1 FROM vIPR WHERE ipr.OpenDate_MST >= '01/01/2013' )` – Dalagrath Jan 09 '14 at 17:18
  • What about this (pseudo query): SELECT CASE WHEN EXISTS( ) THEN ELSE '100%' END AS 'result'; – mcsilvio Jan 09 '14 at 17:25
  • Look at my above query (main question). When I try to enter `WHEN EXISTS(QUERY) THEN (QUERY) ELSE 1.0 END` it gives me a good 4 or 5 errors. – Dalagrath Jan 09 '14 at 17:33
  • Msg 156, Level 15, State 1, Line 53 Incorrect syntax near the keyword 'WITH'. Msg 319, Level 15, State 1, Line 53 Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Msg 319, Level 15, State 1, Line 98 Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near – Dalagrath Jan 09 '14 at 17:58
  • Hmm. Not having any ideas. The only thing I can advise now is to make sure to alias all of your intermediate tables using the 'as' keyword. http://www.w3schools.com/sql/sql_alias.asp This is missing and is usually needed with nested queries. – mcsilvio Jan 09 '14 at 18:49