Date and Times in Escalation Rules

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Date and Times in Escalation Rules

  • Comments 2
  • Likes
I have discussed in a previous article the structure and purpose of the SQL clause of an Escalation rule.

e.g.

(quot_IsQuote is not null) AND UPPER(RTRIM(quot_Status))=UPPER(RTRIM(N'Active')) AND (Escl_DateTime<#T And Escl_UserID=#U)
 
We know that in the SQL trigger clause the #T and #U codes represent the current time and the currently logged on user. If the #T code represents the current time then we can imagine it being substituted as getDate in an implementation on SQL Server.
 
(quot_IsQuote is not null) AND UPPER(RTRIM(quot_Status))=UPPER(RTRIM(N'Active')) AND (Escl_DateTime #T ) and ((oppo_targetclose - 28) < #T)
 
or case_assigneduserid=#U and (case_notifytime+7)>#T

or

DATEDIFF (mi, case_notifytime, getdate()) > 30

Comments
  • Jeff, If we're using SLA and we want the notifications to only happen during business hours, How can this be accomplished?

  • Ken

    Within the SQL of the Escalation where clause

    escl_slaid=1 and escl_datetime<#T and

    (escl_slaactionid=5) and

    case_assigneduserid=#U

    You look to changing the SQL to look that the time returned by the #T is within a certain time period.  You can start to examine the actual time using

    CONVERT (time, GETDATE())