Calculate SLA Breach Times Using SQL

Your SQL Office Hours begins soon… Calculating SLA Breach Times
with SQL Chris Saxon @ChrisRSaxon & @SQLDaily https://www.youtube.com/c/TheMagicofSQL https://blogs.oracle.com/sql

Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| Welcome to Ask TOM Office Hours!

Too many support tickets exceed their SLA! Add breach dates
to my report!

| SLA durations P1 => 4 hours P2 => 8 hours P3 => 22 hours P4 => 44 hours P5 => 55 hours 24 x 7 Business hours 07:00 – 18:00, Monday - Friday

| P1 Wed 8PM P3 Wed Thu Fri 4PM P4 Wed Thu Fri Sat Sun Mon Tue 4PM Tickets Raised Wed 4PM

| P1 Wed 8PM P3 Wed Thu Fri 4PM P4 Wed Thu Fri Sat Sun Mon Tue 4PM Tickets Raised Wed 4PM Non-working Mondays?

| SLAs can change Working days vary Working hours vary Make it data driven! =>

|

| Get Rows • Join tickets to dates table to get a row/day of possible breach dates

| Get Rows • Join tickets to dates table to get a row/day of possible breach dates Calc hours • Running total of SLA hours used by the end of each day

| Get Rows • Join tickets to dates table to get a row/day of possible breach dates Calc hours • Running total of SLA hours used by the end of each day Find breach • SLA hours between current and previous running totals

| Get Rows for Possible Breach Dates select … from tickets t join calendar_dates d on trunc ( raised_datetime ) <= calendar_date and raised_datetime + 20 > calendar_date join priorities p on t.priority_id = p.priority_id

| Get Rows for Possible Breach Dates select … from tickets t join calendar_dates d on trunc ( raised_datetime ) <= calendar_date and raised_datetime + 20 > calendar_date join priorities p on t.priority_id = p.priority_id > max elapsed days to breach

| Calculate Hours Used sum ( ( calendar_date + ( business_close_hour / 24 ) ) - greatest ( ( calendar_date + ( business_start_hour / 24 ) ) , raised_datetime ) ) over ( … ) * 24

| sum ( … ) over ( partition by ticket_id order by calendar_date ) * 24 tot, sum ( … ) over ( partition by ticket_id order by calendar_date rows between unbounded preceding and 1 preceding ) * 24 prev_tot Calculate Hours Used

| sum ( … ) over ( partition by ticket_id order by calendar_date ) * 24 tot, sum ( … ) over ( partition by ticket_id order by calendar_date rows between unbounded preceding and 1 preceding ) * 24 prev_tot Calculate Hours Used Hours used by day end

| sum ( … ) over ( partition by ticket_id order by calendar_date ) * 24 tot, sum ( … ) over ( partition by ticket_id order by calendar_date rows between unbounded preceding and 1 preceding ) * 24 prev_tot Calculate Hours Used Hours used at day start

| Find Breach where sla_hours >= prev_tot and sla_hours < tot

| Get All Breaches with business_only as ( … ), all_hours as ( … ) select … from business_only union all select … from all_hours

| DEMO

| When will I use this?!

Other Use Cases? Predicting goods delivery dates Estimating project completion
times Calculating payment due dates 1 2 3

| Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon if priority in ( 1, 2 ) then breach_datetime := … else breach_datetime := business_hrs_fn; end if;

| Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon for … loop if working_day then … end if; end loop; if Easter then if Christmas then if VE Day then

| "…a code grinder is going to figure it out, while a database programmer is going to spell it out" - Ken Downs https://database-programmer.blogspot.com/2007/11/database-skills-introdution.html

| But what about performance?!

| alter table tickets add breach_datetime date; Store the breach date!

| How to Find the Next Business Day and Add or Subtract N Working Days with SQL https://bit.ly/next-working-day-sql Ask TOM: Adding hours to date, but within boundaries https://bit.ly/add-business-hours Further Reading

| asktom.oracle.com #MakeDataGreatAgain Ryan McGuire / Gratisography

Calculate SLA Breach Times Using SQL

Calculate SLA Breach Times Using SQL

Transcript

More Decks by Chris

Other Decks in Technology

Featured