I stumbled upon this related discussion:
Is there any way to calculate remaining time for SLA violation in BOXI?
I don't know how I missed it in my searches, I used some variation on those tags. Oh, well.
In any event, the Xtraction calculated value syntax is:
CAST((DATEDIFF(SECOND, GETUTCDATE(), DATEADD(second, SLA_EVENT_ATTRIBUTES.FIRE_TIME, '1-JAN-1970')) / 86400) AS varchar(10)) + ' d ' + CAST((DATEDIFF(SECOND, GETUTCDATE(), DATEADD(second, SLA_EVENT_ATTRIBUTES.FIRE_TIME, '1-JAN-1970')) % 86400 / 3600) AS varchar(10)) + ' h ' + CAST(((DATEDIFF(SECOND, GETUTCDATE(), DATEADD(second, SLA_EVENT_ATTRIBUTES.FIRE_TIME, '1-JAN-1970')) % 86400) % 3600 / 60) AS varchar(10)) + ' m'
But this is returning the negative value when status is 12 or 13 (Delayed or Delayed Repeating) because the fire_time is seconds for Delay Remaining and not a date timestamp which is what the above code is assuming.
I was able to verify the following works when the status is 12 or 13:
CAST((SLA_EVENT_ATTRIBUTES.FIRE_TIME / 86400) AS varchar(10)) + ' d ' + CAST((SLA_EVENT_ATTRIBUTES.FIRE_TIME % 86400 / 3600) AS varchar(10)) + ' h ' + CAST(((SLA_EVENT_ATTRIBUTES.FIRE_TIME % 86400) % 3600 / 60) AS varchar(10)) + ' m'
this returns '00 d 00 h 26 m', which matches the displayed value on the ticket (to the minute).
So, I updated the expression for the calculated value of 'SLA Remaining' using this CASE statement::
CASE WHEN SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 2 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 3 THEN CAST((DATEDIFF(SECOND, GETUTCDATE(), DATEADD(second, SLA_EVENT_ATTRIBUTES.FIRE_TIME, '1-JAN-1970')) / 86400) AS varchar(10)) + ' d ' + CAST((DATEDIFF(SECOND, GETUTCDATE(), DATEADD(second, SLA_EVENT_ATTRIBUTES.FIRE_TIME, '1-JAN-1970')) % 86400 / 3600) AS varchar(10)) + ' h ' + CAST(((DATEDIFF(SECOND, GETUTCDATE(), DATEADD(second, SLA_EVENT_ATTRIBUTES.FIRE_TIME, '1-JAN-1970')) % 86400) % 3600 / 60) AS varchar(10)) + ' m' WHEN SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 12 OR SLA_EVENT_ATTRIBUTES.STATUS_FLAG = 13 THEN CAST((SLA_EVENT_ATTRIBUTES.FIRE_TIME / 86400) AS varchar(10)) + ' d ' + CAST((SLA_EVENT_ATTRIBUTES.FIRE_TIME % 86400 / 3600) AS varchar(10)) + ' h ' + CAST(((SLA_EVENT_ATTRIBUTES.FIRE_TIME % 86400) % 3600 / 60) AS varchar(10)) + ' m' ELSE 'N/A' END
Next up: Modifying the check for 'SLA Expiry Date' (fire_time) and 'SLA Breach Warning' to account for this as.well.
J.W.