Auto Scheduling Incidents by Priority Assignment

The good thing about the below incident rules is that they are easy to change for your companies needs. We base our scheduling on priority, however, if you base it off urgency or impact it is not difficult to replace the priority with either of those.

One item to keep in mind is that holidays specified in the rules are US market holidays through 2009. Also, we are a M-F 7-5 Helpdesk Support, so Saturdays and Sundays are excluded within our incident rule.

I am only including 2 of the priority schedules as the others just need to be tweaked to your Service Level Agreements set between you and your customers/end-users. The two included are Low and ASAP. Our ASAP is included because it is "special" in the sense that there are 2 ASAP assignments for us, (1) during market hours and (2) after market hours. We have this since we are a financial firm anything during market that is defined as ASAP is hindering our business.

Low Priority Scheduling Incident Rule

1. Create a new Incident Rule
2. Name your rule.
3. Set these 3 properties separately using the Add button:
   
   1. set Start Date to "workitem_created_on"
   2. set Schedule to "is_scheduled" = true
   3. set "workItem_due_on" to (insert SQL below)

   HDQUERY[[declare @open_at_mins int 
   declare @close_at_mins int 
   declare @sla int 
   declare @holidaylist varchar(1000) 
   declare @weekends varchar(50) 
   
   -- The following must be modified to match your SLA
   -- Set the opening and closing times of business in minutes. (Our business hours for Support is 6 AM to 6 PM ET)
   set @open_at_mins = 6*60 
   set @close_at_mins = 18*60 
   -- The SLA is set in minutes
   set @sla = 480*60 
   -- The dates must be in the format yyyymmdd. These are all market holidays from 2008 to 2009. 
   set @holidaylist = '20080101, 20080121, 20080218, 20080321, 20080526, 20080704, 20080901, 20081127, 20081225, 20090101, 20090119, 20090216, 20090410, 20090525, 20090703, 20090907, 20091126, 20091225'
   -- Days of the week that do not count towards this SLA. The days are specified in a string. 
   -- In the example, Saturday (7) and Sunday(1) are chosen. If the SLA covers every day of the week 
   -- leave this string empty 
   set @weekends = '1,7' 
   --END OF USER-DEFINED SECTION 
   
   
   declare @working_mins int 
   set @working_mins = @close_at_mins - @open_at_mins 
   
   declare @sla_mins int 
   declare @sla_days int 
   set @sla_days = @sla/@working_mins 
   set @sla_mins = @sla%@working_mins 
   
   declare @duedate datetime 
   DECLARE @created_on datetime 
   SELECT @created_on = REPLACE('WORKITEM(workitem_created_on)','Z','') 
   
   SET @created_on = CASE 
   WHEN DATEPART(hh,@created_on)*60+DATEPART(mi,@created_on) < @open_at_mins 
   THEN DATEADD(mi,@open_at_mins,CONVERT(datetime,CONVERT(varchar,@created_on,112))) 
   ELSE @created_on 
   END 
   
   SET @created_on = CASE 
   WHEN DATEPART(hh,@created_on)*60+DATEPART(mi,@created_on) > @close_at_mins 
   OR CHARINDEX(CONVERT(varchar,@created_on,112),@holidaylist)>0 
   OR CHARINDEX(CONVERT(varchar,DATEPART(dw,@created_on)),@weekends)>0 
   THEN DATEADD(mi,@open_at_mins,CONVERT(datetime,CONVERT(varchar,DATEADD(dd,1,@created_on),112))) 
   ELSE @created_on 
   END 
   
   WHILE CHARINDEX(CONVERT(varchar,@created_on,112),@holidaylist)>0 OR CHARINDEX(CONVERT(varchar,DATEPART(dw,@created_on)),@weekends)>0 
   BEGIN 
   SET @created_on = DATEADD(dd,1,@created_on) 
   END 
   
   SET @duedate = CASE 
   WHEN DATEPART(hh,@created_on)*60+DATEPART(mi,@created_on) + @sla_mins > @close_at_mins 
   THEN DATEADD(mi,(24*60)-@working_mins+@sla_mins,@created_on) 
   ELSE DATEADD(mi,@sla_mins,@created_on) 
   END 
   
   WHILE @sla_days > 0 
   BEGIN 
   SET @duedate = DATEADD(dd,1,@duedate) 
   SET @sla_days = @sla_days-1 
   WHILE CHARINDEX(CONVERT(varchar,@duedate,112),@holidaylist)>0 OR CHARINDEX(CONVERT(varchar,DATEPART(dw,@duedate)),@weekends)>0 
   BEGIN 
   SET @duedate = DATEADD(dd,1,@duedate) 
   END 
   END 
   SELECT @duedate]]
   
   

4. set When to "Every time incident is saved"
5. set And function to "When ALL of these are TRUE"
   
   1. "Priority" is equal to "Low"
      • add this using the Add button

Dual ASAP Priority Scheduling Incident Rule

This will schedule an incident's due date/time for 1 hour after creation during 6AM-6PM hours (all in ET) and 2 hours after 6 PM to 6 AM the following day.

1. Create a new Incident Rule
2. Name your rule.
3. Set these 3 properties separately using the Add button:
   
   1. set Start Date to "workitem_created_on"
   2. set Schedule to "is_scheduled" = true
   3. set "workItem_due_on" to (insert SQL below)

   HDQUERY[[declare @open_at_mins int 
   declare @close_at_mins int 
   declare @sla int 
   declare @holidaylist varchar(1000) 
   declare @weekends varchar(50) 
   declare @market int
   
   -- The following must be modified to match your SLA
   -- Set the opening and closing times of business in minutes (in this example they are 
   -- set to 8am and 6pm EST). 
   set @market = datepart(hh,getdate())
   
   if @market >= 8 and @market <= 18 
    begin
    --market hrs
    set @open_at_mins = 8*60 
    set @close_at_mins = 18*60 
    -- The SLA is set in minutes (here it is set to 1 hour) 
    set @sla = 1*60 
    end
   else
    begin 
    -- Set the opening and closing times of business in minutes (in this example they are set to 6pm and 7am EST)
     set @open_at_mins = 18*60 
     set @close_at_mins = 7*60 
     -- The SLA is set in minutes (here it is set to 2 hours) 
     set @sla = 2*60
    end
    
   -- HOLIDAY The dates must be in the format yyyymmdd. 
   -- all Financial holidays from 2008 to 2009. 
   set @holidaylist = '20080101, 20080121, 20080218, 20080321, 20080526, 20080704, 20080901, 20081127, 20081225, 20090101, 20090119, 20090216, 20090410, 20090525, 20090703, 20090907, 20091126, 20091225'
   
   -- Days of the week that do not count towards this SLA. The days are specified in a string. 
   -- In the example, Saturday (7) and Sunday(1) are chosen. If the SLA covers every day of the week 
   -- leave this string empty 
   set @weekends = '1,7' 
   --END OF USER-DEFINED SECTION 
   
   declare @working_mins int 
   set @working_mins = @close_at_mins - @open_at_mins 
   
   declare @sla_mins int 
   declare @sla_days int 
   set @sla_days = @sla/@working_mins 
   set @sla_mins = @sla%@working_mins 
   
   declare @duedate datetime 
   DECLARE @created_on datetime 
   SELECT @created_on = REPLACE('WORKITEM(workitem_created_on)','Z','') 
   
   SET @created_on = CASE 
   WHEN DATEPART(hh,@created_on)*60+DATEPART(mi,@created_on) < @open_at_mins 
   THEN DATEADD(mi,@open_at_mins,CONVERT(datetime,CONVERT(varchar,@created_on,112))) 
   ELSE @created_on 
   END 
   
   SET @created_on = CASE 
   WHEN DATEPART(hh,@created_on)*60+DATEPART(mi,@created_on) > @close_at_mins 
   OR CHARINDEX(CONVERT(varchar,@created_on,112),@holidaylist)>0 
   OR CHARINDEX(CONVERT(varchar,DATEPART(dw,@created_on)),@weekends)>0 
   THEN DATEADD(mi,@open_at_mins,CONVERT(datetime,CONVERT(varchar,DATEADD(dd,1,@created_on),112))) 
   ELSE @created_on 
   END 
   
   WHILE CHARINDEX(CONVERT(varchar,@created_on,112),@holidaylist)>0 OR CHARINDEX(CONVERT(varchar,DATEPART(dw,@created_on)),@weekends)>0 
   BEGIN 
   SET @created_on = DATEADD(dd,1,@created_on) 
   END 
   
   SET @duedate = CASE 
   WHEN DATEPART(hh,@created_on)*60+DATEPART(mi,@created_on) + @sla_mins > @close_at_mins 
   THEN DATEADD(mi,(24*60)-@working_mins+@sla_mins,@created_on) 
   ELSE DATEADD(mi,@sla_mins,@created_on) 
   END 
   
   WHILE @sla_days > 0 
   BEGIN 
   SET @duedate = DATEADD(dd,1,@duedate) 
   SET @sla_days = @sla_days-1 
   WHILE CHARINDEX(CONVERT(varchar,@duedate,112),@holidaylist)>0 OR CHARINDEX(CONVERT(varchar,DATEPART(dw,@duedate)),@weekends)>0 
   BEGIN 
   SET @duedate = DATEADD(dd,1,@duedate) 
   END 
   END 
   SELECT @duedate]] 
   --the above is all run off the stipulation that the asap priority assignment has been made
   when priority = 'ASAP'
   
   

4. set When to "Every time incident is saved"
5. set And function to "When ALL of these are TRUE"
   
   1. "Priority" is equal to "ASAP"
      • add this using the Add button

GOOD LUCK!