Calculate SLA Breach Times Using SQL

Transcript

1. 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
2. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Welcome to Ask TOM Office Hours!
3. Too many support tickets exceed their SLA! Add breach dates to my report!
4. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | 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
5. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | P1 Wed 8PM P3 Wed Thu Fri 4PM P4 Wed Thu Fri Sat Sun Mon Tue 4PM Tickets Raised Wed 4PM
6. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | P1 Wed 8PM P3 Wed Thu Fri 4PM P4 Wed Thu Fri Sat Sun Mon Tue 4PM Tickets Raised Wed 4PM
7. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | P1 Wed 8PM P3 Wed Thu Fri 4PM P4 Wed Thu Fri Sat Sun Mon Tue 4PM Tickets Raised Wed 4PM
8. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | P1 Wed 8PM P3 Wed Thu Fri 4PM P4 Wed Thu Fri Sat Sun Mon Tue 4PM Tickets Raised Wed 4PM
9. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | P1 Wed 8PM P3 Wed Thu Fri 4PM P4 Wed Thu Fri Sat Sun Mon Tue 4PM Tickets Raised Wed 4PM
10. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | P1 Wed 8PM P3 Wed Thu Fri 4PM P4 Wed Thu Fri Sat Sun Mon Tue 4PM Tickets Raised Wed 4PM Non-working Mondays?
11. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | SLAs can change Working days vary Working hours vary Make it data driven! =>
12. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. |
13. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Get Rows • Join tickets to dates table to get a row/day of possible breach dates
14. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | 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
15. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | 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
16. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | 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
17. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | 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
18. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Calculate Hours Used sum ( ( calendar_date + ( business_close_hour / 24 ) ) - greatest ( ( calendar_date + ( business_start_hour / 24 ) ) , raised_datetime ) ) over ( … ) * 24
19. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Calculate Hours Used sum ( ( calendar_date + ( business_close_hour / 24 ) ) - greatest ( ( calendar_date + ( business_start_hour / 24 ) ) , raised_datetime ) ) over ( … ) * 24
20. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Calculate Hours Used sum ( ( calendar_date + ( business_close_hour / 24 ) ) - greatest ( ( calendar_date + ( business_start_hour / 24 ) ) , raised_datetime ) ) over ( … ) * 24
21. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | 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
22. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | 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
23. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | 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
24. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Find Breach where sla_hours >= prev_tot and sla_hours < tot
25. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Get All Breaches with business_only as ( … ), all_hours as ( … ) select … from business_only union all select … from all_hours
26. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | DEMO
27. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | When will I use this?!
28. Other Use Cases? Predicting goods delivery dates Estimating project completion times Calculating payment due dates 1 2 3
29. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | 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;
30. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | 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
31. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | "…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
32. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | But what about performance?!
33. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | alter table tickets add breach_datetime date; Store the breach date!
34. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | 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
35. Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | asktom.oracle.com #MakeDataGreatAgain Ryan McGuire / Gratisography