Calculating Business Impact of Service Outages

Recently someone described how they were keeping track of service outages and while the list itself was useful to get an idea of the total volume of outages, it did not answer the simple qusetion that was: What percentage of this month / quarter / year did the service function as expected?

Background Context

Let's say you are, at a minimum, keeping track of when outages started and when they ended. This example can be expanded to provide more levels of detail like outage severity, areas of business affected, etc., but all you need at a minimum is just the following:

The Calculation Problem

Excel can easily tell you that these incidents covered five, two, three days respectively, but of course it would be wrong to say that ten days in January were affected due to the incident overlap. Calculating actual time coverage is a lot easier in SQL. As a workaround, here is an Excel formula to generate a valid SQL CTE you can use to copy spreadsheet data like that into a database engine without having to import CSVs or requring any table privileges at all.

=
  "SELECT '" &
  A2 &
  "' as ticket_id, '" &
  text(B2,"yyyy-mm-dd") &
  "'::date as start_date, '" &
  text(C2,"yyyy-mm-dd") &
  "'::date as end_date"

This will produce a line like this:

SELECT 
  'ABC' as ticket_id, 
  '2026-01-01'::date as start_date, 
  '2026-01-05'::date as end_date

Note for Microsoft SQL Server

MS SQL Server is of course the Internet Explorer of databases and lags behind in common data casting and does not allow '2026-01-01'::date. It requires CAST('2026-01-01' as DATE) instead, making the excel formula this instead:

=
  "SELECT '" &
  A2 &
  "' as ticket_id, CAST('" &
  text(B2,"yyyy-mm-dd") &
  "' AS DATE) as start_date, CAST('" &
  text(C2,"yyyy-mm-dd") &
  "' AS DATE) as end_date"

Making the CTE

Now you can simply turn these individual lines into a long union block using the join function like this =JOIN(A2:A5," UNION ALL ") for Google Sheets or =TEXTJOIN(A2:A5, FALSE, " UNION ALL ") for Microsoft Excel. Now you have this valid SQL:

SELECT 'ABC' as ticket_id, '2026-01-01'::date as start_date, '2026-01-05'::date as end_date 
UNION ALL 
SELECT 'DEF' as ticket_id, '2026-01-03'::date as start_date, '2026-01-04'::date as end_date 
UNION ALL 
SELECT 'GHI' as ticket_id, '2026-01-05'::date as start_date, '2026-01-07'::date as end_date

Making a Date Spline

Most data warehouses already have a date spline or dim_dates table that has a list of dates to be used in metric calculation. If not, here a couple basic templates for some common database engines:

PostgreSQL

SELECT generate_series(
    DATE_TRUNC('year', CURRENT_DATE),
    CURRENT_DATE,
    INTERVAL '1 day'
	)::date AS dt

MS SQL Server

WITH DateSeries AS (
    -- Anchor member: Start at January 1st of the current year
    SELECT CAST(DATEFROMPARTS(YEAR(GETDATE()), 1, 1) AS DATE) AS CalendarDate
    
    UNION ALL
    
    -- Recursive member: Add one day until we reach today
    SELECT DATEADD(DAY, 1, CalendarDate)
    FROM DateSeries
    WHERE CalendarDate < CAST(GETDATE() AS DATE)
)
SELECT 
    CalendarDate
FROM DateSeries
OPTION (MAXRECURSION 366); 
-- MAXRECURSION 366 ensures the query handles leap years without error

Snowflake

SELECT 
    DATEADD(DAY, SEQ4(), DATE_TRUNC('YEAR', CURRENT_DATE())) AS CALENDAR_DATE
FROM TABLE(GENERATOR(ROWCOUNT => 366)) 
WHERE CALENDAR_DATE <= CURRENT_DATE()
ORDER BY CALENDAR_DATE;

Putting it All Together

Having a table with the incidents and the date spline, we can easily bring this all together like this:

with tickets as (
  -- paste your ticket SQL here
)
,
date_spline as (
  -- paste your date spline sql here, 
  --omit if you have date spline table to reference from somewhere
)
,
combiniation as (
    select 
      d.dt, t.ticket_id
    from date_spline d
    join tickets t 
      on d.dt >= t.start_date 
      and d.dt <=  t.end_date 
)
select 
  dt, 
  count(distinct ticket_id) 
from combination 
group by 1 
order by 1
;

This will output the following:

Combination Explanation

By starting from the date spline we can inner join the ticket table with the join condition being that the date spline's dt column has to be greater than or equal to a ticket's start_date and smaller than or equal to a ticket's end_date.

This effectively expands the start_date / end_date style table into a list of dates a given ticket was active for. This makes it trivial to use aggregates to get the unique list of dates that had one or more active tickets.

This also means you can easily expand this model with extra dimensions like ticket severity, or what business functions were impacted. It simply has to be in the tickets table and can then be used in GROUP BY or WHERE clauses at the end.

The GROUP BY dt part will handle the date "duplication" issue, so when you run something like COUNT(dt) you will correctly get 7 affected days instead of 10 that summing up the date diff on tickets alone would give you.

Conclusion

While this was a simplified example, the general logic stands for more complex scenarios such as calculating hours with outages instead of days or advanced ticket segmentation. It also is a query you build once and only swap the ticket data SQL if you need to re-run the report. Naturally doing this all from a warehouse to start makes it easier, but you can even do this with a spreadsheet data source. As long as you don't drop thousdands of rows of tickets into that first query you don't even have to worry about performance issues -- even on MS SQL Server ;)