WoW Challenge Week 05 — Case SLA — Calculation on Date

I published an article last month regarding the date SAQL and binding, and today I came across the new challenge for week 5 from Workout Wednesday — 2024 Week 05 — Case SLAs. This challenge also test on the date level but focuses on a more granular level, involving hours and minutes, which is a new and enjoyable experience for me. I couldn’t resist working on it until I found a solution that I’m excited to share with you.

The task is to use SAQL to generate a dashboard looks like this:

For detailed requirements, please refer to the 2024 Week 05 — Case SLAs article from WoW.

The first involves setting the bar length based on the number of hours a case remains “open.” This part isn’t difficult. By converting the date to epoch format, subtracting, and then converting back to hours, we can create the correct bar chart.

q = load "cases";
q = group q by ('ID','Case_Title');
q = foreach q generate 'ID','Case_Title'
    //1 hour is 3600 seconds so we divide 3600 to get the hours result
    , (min('Close_Date_sec_epoch') - min('Open_Date_sec_epoch'))/3600 as 'Hours Open'

The graph looks like this, and the numbers match the expected result.

The part that gives me a hard time is the SLA calculation. The requirement is to have the SLA starts counting at midnight of the following day (ie. If I open a case at 4pm, the SLA clock doesn’t start ticking for another 8 hours.) I thought I could extract the year, month, and date and add one more day since the SLA starts at midnight the next day. However, I couldn’t perform calculations on the date, or at least I didn’t know how.

I broke down the ask to a visual graph. Between the open date and the closed date, we have the open hours (Part A). As the SLA starts at midnight of the following day, we don’t begin counting until day 1. Therefore, from day 1 to the closed date represents the SLA time we need (Part B). We must calculate the difference, which is Part C, representing the time remaining for Day 0.

After some trial and error, I managed to revert to the epoch level, obtain the epoch for hours, minutes, and seconds accordingly, and add them up together. This gave me the total seconds for the open date’s hour, minute, and second. Then I could subtract this number from one day’s seconds to determine how many seconds are left for the open day, which is Part C.

Here is the code:

q = load "cases";
q = foreach q generate 'ID','Case_Title', 'Open_Date', 'Close_Date'
    , 86400 //second in a day
     - (string_to_number(toString(toDate(Open_Date_sec_epoch),"HH"))*3600  //second for the open day hours
     + string_to_number(toString(toDate(Open_Date_sec_epoch),"mm"))*60   //second or the open day minutes
     + string_to_number(toString(toDate(Open_Date_sec_epoch),"ss"))) //second or the open day seconds
     as 'Time_Left_forday0' 
    , min('Close_Date_sec_epoch') - min('Open_Date_sec_epoch') as 'Total_Sec'
    , (min('Close_Date_sec_epoch') - min('Open_Date_sec_epoch'))/3600 as 'Hours Open'
    , sum('SLA')*24 as 'SLA';

Using the ‘Time_Left_forday0’ field (part C), we can calculate the actual hours in between (part B). We can then determine the SLA status to see whether the case is taken within the SLA or not based on part B — ‘Hours’.

q = group q by ('ID','Case_Title');
q = foreach q generate 'ID','Case_Title'
    , min('Hours Open') as 'Hours Open'
    , (min('Total_Sec') - min('Time_Left_forday0')) /3600 as 'Hours'
    , min('SLA') as 'SLA'
    , case when (min('Total_Sec') - min('Time_Left_forday0')) /3600 > min('SLA') 
      then "SLA not met" else "SLA met" end as 'SLA Status'

Final result:

Enjoy SAQL 😁!