Skip to Main Content

Write Great SQL Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

April 21

13:00 UTC   Start Times Around the World

Subscribe to be notified of changes to sessions and give us feedback!

Having trouble watching the video on this page? Open the video in your browser.


Calculating SLA Breach Times with SQL
Working on support tickets is a task most of us in the IT industry are familiar with. Ensuring these are handled within their SLA is key part of these assignments.

For high priority tickets that are worked on 24x7, calculating the SLA breach date is simply a matter of adding the working duration to the raised date.

But how do you calculate the SLA breach times for tickets that are only handled during working hours?

These need to skip non-working days such as weekends and take into account business opening hours. You can do this with SQL. This session shows you how to use analytic functions to handle non-working days.

Watch this session to learn how to use SQL to calculate "next working day" algorithms!

Highlights include:

01:50 - Introduction to the problem
13:00 - Discussing the data model to solve this problem
14:30 - Outline of the algorithm to calculate breach dates
16:55 - Walking through the SQL to do the calculation
28:45 - Demo of the SQL in action
38:00 - Discussion of other use cases
42:50 - A code grinder vs a database programmer
44:15 - Performance implications for the SQL method
50:22 - Resources for further reading

Your Experts

Chris Saxon
Chris Saxon, Oracle Developer Advocate for SQL    
Chris Saxon is an Oracle Developer Advocate and quizmaster on Dev Gym. His job is to help you get the best out of the Oracle Database and have fun with SQL! He's also part of the answer team on Ask TOM and creates YouTube videos at The Magic of SQL channel.
Keith Laker
Keith Laker, Senior Principal Product Manager    
I have been working with Oracle data warehouse technology for over 20 years working on a wide variety of data warehouse projects both as a consultant and an onsite support engineer. I am now part of the Data Warehouse Product Management Team where I am responsible for analytical SQL. I am based in the UK at our Manchester office. A key part of my role is to work with our sales teams to brief our customers on data warehousing and analytical SQL: explaining the wide variety of new and exciting opportunities that our DW and analytical solutions can support. I regularly deliver sales training for data warehousing and analytical SQL across all our sales regions and provide competitive intelligence support across all the major data warehouse vendors.