Problem:
We are working on a report, which displays tickets related metrics, based off of a custom Oracle view. TICKET's can be OPENED with any PRIORITY at any time of the day 24 x 7 x 365.
We have imposed an SLA on this ticket, there are 5 levels the tickets can be OPENED. P1 must be CLOSED in 4 hours, P2 in 8 Hours, P3 in 22 business hours, P4 in 44 business hours while P5 in 55 business hours.
Tickets with P1 and P2 are treated as if "business hours" were 24 hours a day, seven days a week, with no exceptions.
Users can also create P3, P4, and P5 any anytime of the day though, but the counter starts from 7 am and ends at 6 pm on workdays, as it is expected that the employee will resolve this ticket between the hours of 7 am to 6 pm, excluding weekends and holidays.
We maintain a DATE table, which has a flag with value 0 if it's a weekends/holidays
CREATE TABLE DIM_FPDATE
(
DAY_HOLDER DATE ,
PERIOD_D VARCHAR2(6) ,
PERIOD_C VARCHAR2(7) ,
AGING_FLAG DECIMAL(1,0) ,
DAY_DESCRIPTION VARCHAR2(100) ,
CONSTRAINT DIM_FPDATE_PK PRIMARY KEY (DAY_HOLDER)
);
INSERT
INTO
"DIM_FPDATE"
(
DAY_HOLDER,
PERIOD_D ,
PERIOD_C,
AGING_FLAG,
DAY_DESCRIPTION
)
WITH
SM AS
(
SELECT
to_date ('01-JAN-2017', 'dd-mon-yyyy') + rownum - 1 DAY_HOLDER
FROM
DUAL
CONNECT BY LEVEL < 1 + TO_DATE ('01-JAN-2025', 'dd-mon-yyyy') - TO_DATE (
'01-JAN-2015', 'dd-mon-yyyy') +1
)
SELECT
DAY_HOLDER ,
TO_CHAR(DAY_HOLDER,'YYYYMM') PERIOD_D ,
TO_CHAR(DAY_HOLDER,'MM/YYYY') PERIOD_C ,
CASE
WHEN TO_CHAR(DAY_HOLDER,'DY') IN ('SAT','SUN')
THEN 0
ELSE 1
END AGING_FLAG ,
CASE
WHEN TO_CHAR(DAY_HOLDER,'DY') IN ('SAT','SUN')
THEN TO_CHAR(DAY_HOLDER,'Dy')
||', Weekend'
ELSE TO_CHAR(DAY_HOLDER,'Dy')
||', ACI Working Day'
END DAY_DESCRIPTION
FROM
SM;
--Update Company Holiday for 4th of Jul 20
UPDATE
DIM_FPDATE
SET
AGING_FLAG = 0 ,
DAY_DESCRIPTION = TO_CHAR(DAY_HOLDER,'Dy')
||', Independence Day'
WHERE
DAY_HOLDER = '03-JUL-2020';
The User needs now a new column, which will have the exact date/time when the ticket would miss its SLA. For e.g
For P1 we can just add 4 hours to the OPEN_DATE, while for P2 add 8 hours, it's for P3, P4, and P5, where I am finding it tricky as we have to restrict our windows between 7, am to 6 pm and excludes weekends and holidays.
So for P3 tickets, if it was OPENED at 06-MAR-2020 01:00:00, it should be RESOLVED by 09-MAR-2020 18:00:00 (For P3 it's 22 hours (2 days - 7 am to 6 pm: 11 hours). 06-MAR-2020 01:00:00 is Friday, User will start at 7 am, so will have 11 hours for Friday, following Sat/Sun will not be considered and on Monday again he/she will have 11 hours to get it resolved unless Monday is a Holiday. So ticket 3000 should be resolved by 09-MAR-2020 18:00:00.
Sample Sql:
WITH DATA_SET AS (
SELECT 1000 TICKET_NO, 1 PRIORITY, TO_DATE('03-MAR-2020 07.00.00','DD-MON-YYYY HH24.MI.SS') SUBMITTED_DATE from dual
UNION ALL
SELECT 2000 TICKET_NO, 2 PRIORITY, TO_DATE('03-MAR-2020 09.00.00','DD-MON-YYYY HH24.MI.SS') SUBMITTED_DATE FROM DUAL
UNION ALL
SELECT 3000 TICKET_NO, 3 PRIORITY, TO_DATE('06-MAR-2020 01.00.00','DD-MON-YYYY HH24.MI.SS') SUBMITTED_DATE from dual
UNION ALL
SELECT 4000 TICKET_NO, 4 PRIORITY, TO_DATE('02-JUL-2020 12.00.00','DD-MON-YYYY HH24.MI.SS') SUBMITTED_DATE FROM DUAL
UNION ALL
SELECT 5000 TICKET_NO, 5 PRIORITY, TO_DATE('26-FEB-2020 01.00.00','DD-MON-YYYY HH24.MI.SS') SUBMITTED_DATE from dual
)
SELECT
A.*, CASE
WHEN PRIORITY = 1 THEN SUBMITTED_DATE + 4/24
WHEN PRIORITY = 2 THEN SUBMITTED_DATE + 8/24
WHEN PRIORITY = 3 THEN SUBMITTED_DATE + 2
WHEN PRIORITY = 4 THEN SUBMITTED_DATE + 4
WHEN PRIORITY = 5 THEN SUBMITTED_DATE + 5
ELSE sysdate END EXPECTED_CLOSE_TIME
from data_set A;So the output (EXPECTED_CLOSE_TIME) we are expecting is
TICKET_NO PRIORITY SUBMITTED_DATE EXPECTED_CLOSE_TIME
1000 1 03-MAR-2020 07.00.00 03-MAR-2020 11.00.00
2000 2 03-MAR-2020 09.00.00 03-MAR-2020 17.00.00
3000 3 06-MAR-2020 01.00.00 09-MAR-2020 18.00.00
4000 4 02-JUL-2020 13.00.00 09-JUL-2020 13.00.00
5000 5 26-FEB-2020 01.00.00 03-MAR-2020 18.00.00
Thanks,
Saby
OK, here you go:
First up, I'd create a table of priorities storing the SLA in hours. And add start/end hour columns to the dates table:
alter table DIM_FPDATE
add (
business_start_hour int,
business_close_hour int
);
create table priorities (
priority_id integer not null primary key,
sla_hours integer not null,
business_hours_only varchar2(1) not null
check ( business_hours_only in ( 'Y', 'N' ) )
);
insert into priorities values ( 1, 4, 'N' );
insert into priorities values ( 2, 8, 'N' );
insert into priorities values ( 3, 22, 'Y' );
insert into priorities values ( 4, 44, 'Y' );
insert into priorities values ( 5, 55, 'Y' );
update dim_fpdate
set business_start_hour = 7,
business_close_hour = 18
where to_char ( day_holder, 'dy' ) in (
'mon', 'tue', 'wed', 'thu', 'fri'
)
and aging_flag = 1;
commit;This makes it easier to change the SLAs & deal with one-off changes to working hours (e.g. finishing early on Christmas Eve). It also makes the query a lot easier to write :)
Calculating P1 & 2 breach times is trivial, so for simplicity I'm ignoring them. It's probably easiest to find these in a separate subquery. Then union (all) together with the other priorities.
In outline, what you want to do is find:
- The running total for the number of working hours elapsed for the current and previous (working) date
- Find the day where the SLA hours falls between these two values
To find the number of SLA hours consumed in a day, you need to find:
The datetime work stops ( date + business_close_hour ) MINUS
The largest of:
submit date
The datetime work starts ( date + business_start_hour )
Then SUM these as an analytic:
partition by ticket_no
order by day_holder
To find the previous hour total, set the window to:
rows between unbounded preceding and 1 preceding
Altogether this gives:
with data_set as (
select 3000 ticket_no, 3 priority, to_date('06-mar-2020 07.00.00','dd-mon-yyyy hh24.mi.ss') submitted_date from dual
union all
select 4000 ticket_no, 4 priority, to_date('02-jul-2020 13.00.00','dd-mon-yyyy hh24.mi.ss') submitted_date from dual
union all
select 5000 ticket_no, 5 priority, to_date('26-feb-2020 01.00.00','dd-mon-yyyy hh24.mi.ss') submitted_date from dual
), days as (
SELECT
A.*,
round (
sum (
( day_holder + ( business_close_hour / 24 ) ) -
greatest (
( day_holder + ( business_start_hour / 24 ) ) ,
submitted_date
)
) over (
partition by ticket_no
order by day_holder
) * 24
) tot,
round (
sum (
( day_holder + ( business_close_hour / 24 ) ) -
greatest (
( day_holder + ( business_start_hour / 24 ) ) ,
submitted_date
)
) over (
partition by ticket_no
order by day_holder
rows between unbounded preceding and 1 preceding
) * 24
) prev_tot, d.*, sla_hours
from data_set A
join dim_fpdate d
on trunc ( SUBMITTED_DATE ) <= day_holder
and submitted_date + 10 > day_holder
join priorities p
on a.priority = p.priority_id
)
select ticket_no,
priority,
submitted_date,
day_holder + ( business_start_hour / 24 )+
( ( sla_hours - prev_tot ) / 24 ) breach_datetime
from days d
where sla_hours > prev_tot
and sla_hours <= tot
order by ticket_no;
TICKET_NO PRIORITY SUBMITTED_DATE BREACH_DATETIME
3000 3 06-MAR-2020 07:00:00 09-MAR-2020 18:00:00
4000 4 02-JUL-2020 13:00:00 09-JUL-2020 13:00:00
5000 5 26-FEB-2020 01:00:00 03-MAR-2020 18:00:00 In the dates to tickets join I've added 10 days to the submit date:
on trunc ( SUBMITTED_DATE ) <= day_holder
and submitted_date + 10 > day_holder
This number of days this is somewhat arbitrary. But it needs to at least as many days for your longest SLA (5), plus however many non-working days in a row you can have.
Set it too low and the query won't work! So err on the side of caution here and pick a bigger value ;)
I discuss this problem further and discuss other options in this article on adding N working days to a date:
https://blogs.oracle.com/sql/how-to-find-the-next-business-day-and-add-or-subtract-n-working-days-with-sql