AnsweredAssumed Answered

Complicated Calcuation Help Needed

Question asked by xxx202xxx on Aug 22, 2017
Latest reply on Aug 29, 2017 by xxx202xxx

Issue: I hope I can explain what I'm attempting adequately enough.  Ultimately I'm trying to return a number for SLA1 +  SLA2 / Total ResolvedCritical tickets.  Bits and pieces of code from some of my other post I was attempting to use, so here we go. 

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SLA1 = Priority::Critical and Status::Resolved tickets that is <= 04:00:00 from Sunday 20:00:00 - Friday 19:00:00 every week for a given Month. Here is the calculation for the week not sure how to do this for the month.

 

Let ( [ T = Get ( CurrentDate ) ;
         dw = DayOfWeek ( T ) ;
         StartTS = If ( dw < 3 or dw > 5 ; Timestamp ( T - dw ; Time ( 16 ; 0 ; 0 ) ) ; Timestamp ( T - 1 ; Time ( 16 ; 0 ; 0 ) ) );

         EndTS = If ( dw < 3 or dw > 5 ; Timestamp ( T - dw + 2 ; Time ( 19 ; 0; 0 )) ; Timestamp ( T ; Time ( 19 ; 0 ; 0 ) ) )
       ] ;

ExecuteSQL ("
SELECT COUNT (Tickets_ID)
FROM Service_Management
WHERE Status = ? AND Priority = ? AND Time_Resolved >= ? AND Time_Resolved <= ? AND c_Resolved_Elapsed<=?

"; " - " ; "" ; "Resolved"; "Critical"; StartTS ; EndTS; "04:00:00" )
) // Let

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SLA2 = Priority::Critical and Status::Resolved tickets that is <= 06:00:00 from Friday 19:00:00 - Sunday 20:00:00 every week for a given Month. Not sure how to set this Start/EndTS for the entire Month

 

Let ( [ T = Get ( CurrentDate ) ;

         dw = DayOfWeek ( T ) ;

         StartTS = ???????

          EndTS = ???????
       ] ;

ExecuteSQL ("
SELECT COUNT (Tickets_ID)
FROM Service_Management
WHERE Status = ? AND Priority = ? AND Time_Resolved >= ? AND Time_Resolved <= ? AND c_Resolved_Elapsed<=?

"; " - " ; "" ; "Resolved"; "Critical"; StartTS ; EndTS; "06:00:00" )
) // Let

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Total Resolved Tickets = Priority::Critical and Status::Resolved tickets every week for a given Month. Here is my calculation for this part.

 

ExecuteSQL ("

SELECT COUNT (Tickets_ID)

FROM Service_Management

WHERE Status = ? AND Priority = ? AND Time_Resolved >= ? AND Time_Resolved <= ?

 

"; " - " ; "" ; "Resolved"; "Critical")

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Outcomes