Calculate SLA Breach Times Using SQL

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Slide 7

Slide 7 text

Slide 8

Slide 8 text

Slide 9

Slide 9 text

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Slide 13

Slide 13 text

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

Slide 20

Slide 20 text

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

Slide 23

Slide 23 text

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Slide 26

Slide 26 text

Slide 27

Slide 27 text

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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;

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

Slide 33

Slide 33 text

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text