Automatically Close Ticket After 24 Hours in Resolved Status

We have something similar in place, but we do a two phase approach. Basically, all workers "resolve" tickets, then we let the users close them through a task that is shown to them. For our notifications, we send an e-mail on setting status to resolution, then after 2 business days we send another reminder that their ticket will close if we don't hear from them. On the 3rd business day we close the ticket. To do this, we use a function we added.

create function dbo.F_GET_WORK_DAYS
	(
	@START_DATE		datetime,
	@END_DATE		datetime
	)
returns int
as
/*
Find the total workdays (Monday to Friday) between two dates,
including the start and end date.
*/

begin

declare @LAST_WEEK_START	datetime
declare	@WHOLE_WEEKS		int
declare	@WHOLE_WEEK_WORKDAYS	int
declare @LAST_WEEK_START_DW	int
declare @LAST_WEEK_DAYS		int
declare @LAST_WEEK_WORKDAYS	int
declare @WORKDAYS		int

set @WHOLE_WEEKS = (datediff(dd,@START_DATE,@END_DATE)+1)/7

set @WHOLE_WEEK_WORKDAYS =  @WHOLE_WEEKS*5

set @LAST_WEEK_START = dateadd(dd,@WHOLE_WEEKS*7,@START_DATE)

set @LAST_WEEK_START_DW = datediff(dd,'17530101',@LAST_WEEK_START)%7

set @LAST_WEEK_DAYS = datediff(dd,@LAST_WEEK_START,@END_DATE)+1

set @LAST_WEEK_WORKDAYS = @LAST_WEEK_DAYS -
	case
	when @LAST_WEEK_DAYS = 0	then 0
	when @LAST_WEEK_START_DW = 0 and @LAST_WEEK_DAYS > 6 	then 2
	when @LAST_WEEK_START_DW = 0 and @LAST_WEEK_DAYS > 5 	then 1
	when @LAST_WEEK_START_DW = 0 				then 0
	when @LAST_WEEK_START_DW = 1 and @LAST_WEEK_DAYS > 5 	then 2
	when @LAST_WEEK_START_DW = 1 and @LAST_WEEK_DAYS > 4 	then 1
	when @LAST_WEEK_START_DW = 1 				then 0
	when @LAST_WEEK_START_DW = 2 and @LAST_WEEK_DAYS > 4 	then 2
	when @LAST_WEEK_START_DW = 2 and @LAST_WEEK_DAYS > 3 	then 1
	when @LAST_WEEK_START_DW = 2 				then 0
	when @LAST_WEEK_START_DW = 3 and @LAST_WEEK_DAYS > 3 	then 2
	when @LAST_WEEK_START_DW = 3 and @LAST_WEEK_DAYS > 2 	then 1
	when @LAST_WEEK_START_DW = 3 				then 0
	when @LAST_WEEK_START_DW = 4 and @LAST_WEEK_DAYS > 2 	then 2
	when @LAST_WEEK_START_DW = 4 and @LAST_WEEK_DAYS > 1 	then 1
	when @LAST_WEEK_START_DW = 4 				then 0
	when @LAST_WEEK_START_DW = 5 and @LAST_WEEK_DAYS > 1 	then 2
	when @LAST_WEEK_START_DW = 5 and @LAST_WEEK_DAYS > 0 	then 1
	when @LAST_WEEK_START_DW = 5 				then 0
	when @LAST_WEEK_START_DW = 6 and @LAST_WEEK_DAYS > 6 	then 2
	else 1
	end

set @WORKDAYS = @WHOLE_WEEK_WORKDAYS + @LAST_WEEK_WORKDAYS

return @WORKDAYS

end

We put this in the Altiris database. It basically is used to compute time between two dates. Our Notification Policy query looks like this:

select  *   
from dbo.hd_workitem_current_view    
where dbo.f_get_work_days(workitem_modified_on, GetDate()) >2   
and workitem_status_lookup_value like 'Resolved'   
			

As you can see it basically passed the modified on date as well as what the current time is and looks for any value greater then 2. This allows us to utilize business days for computations.

I've been toying around with doing this very idea as I begin to build our formal helpdesk. I wanted to have the workers Resolve rather than close the tickets.

I had gotten as far as generating a notification to the user that the ticket had been resolved but the initial approach I was taking was to have 2 links in the e-mail; one for the user to confirm the issue is resolved and close the ticket and the other to indicate it was not yet resolved.

I got the second working even to the point it would go back to the guest console and allow the user to post new comments and then when they submitted, the ticket went back to Open status.

I was having trouble getting the outright close the ticket one to work how I liked, but upon seeing this approach of auto closure, it makes more sense and will be easier to implement.

I will certainly use both of these to set this up.

Good job.

Nice George! I'm sure a lot of people had this on their radar but hadn't dug into it yet. Good to see some SQL..I am strangely fond of it for some reason, I think it's because of its power *evil grin*