SQL & PL/SQL

Hello

Once again need some help/advice on how to go about this SQL logic, I figured out somewhat of it but want to explore a better way of doing the same.

I am trying to calculate the number of hours passed for a ticket when it was OPEN, the goal is to CLOSE the ticket before it hits the SLA.

This is our criteria for SLA determination:

1. if PRIORITY = 1, then the ticket is CRITICAL must be closed in 4 hours irrespective at what day/time it was OPEN
2. if PRIORITY = 2, then the ticket is URGENT must be closed in 8 hours irrespective at what day/time it was OPEN
3. if PRIORITY = 3, then the ticket is HIGH must be closed in 2 days, days for this ticket is between the hours of 7 am to 6 pm, excludes weekends too
4. if PRIORITY = 4, then the ticket is MEDIUM must be closed in 4 days, days for this ticket is between the hours of 7 am to 6 pm, excludes weekends too
5. if PRIORITY = 5, then the ticket is LOW must be closed in 5 days, days for this ticket is between the hours of 7 am to 6 pm, excludes weekends too

We can assume we have a DATE table, which will have a FLAG, with values 1 (Business day)  and 0 (for weekend and Holiday)

The goal is to have this data in some view and send REPORT to the Assigned User as a reminder to close the ticket before it hits the SLA. Here is the sample data, you can change the SUBMITTED_DATE value to test various case for various PRIORITY scenario.

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 10.00.00','DD-MON-YYYY HH24.MI.SS') SUBMITTED_DATE FROM DUAL

UNION ALL

SELECT 3000 TICKET_NO, 3 PRIORITY, TO_DATE('01-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('01-MAR-2020 01.00.00','DD-MON-YYYY HH24.MI.SS') SUBMITTED_DATE FROM DUAL

UNION ALL

SELECT 5000 TICKET_NO, 5 PRIORITY, TO_DATE('01-MAR-2020 01.00.00','DD-MON-YYYY HH24.MI.SS') SUBMITTED_DATE from dual

)

SELECT TICKET_NO, PRIORITY, SUBMITTED_DATE,

CASE

WHEN  PRIORITY = 1 AND ABS(24 *(SUBMITTED_DATE - SYSDATE))  < 4 THEN  ROUND((24 *(SUBMITTED_DATE+4/24 - SYSDATE)),2) || ' Hours remains for P1'

WHEN  PRIORITY = 2 AND ABS(24 *(SUBMITTED_DATE - SYSDATE))  < 8 THEN  ROUND((24 *(SUBMITTED_DATE+8/24 - SYSDATE)),2) || ' Hours remains for P2'

WHEN  PRIORITY = 3 AND (TRUNC(SYSDATE)-TRUNC(SUBMITTED_DATE)) < 2 THEN   (TRUNC(SUBMITTED_DATE+2) - TRUNC(SYSDATE)) || ' Days Remain for P3'

ELSE

'SLA MISSED' END TICKET_ELAPSED_TIME

FROM

DATA_SET;

Thanks,

Saby