2
Vote

Add completed date to base config

description

To do some SLA tracking, we've added some custom fields and triggers to DB to track when an issue goes into completed status.
 
The way we do it is by assigning a time to each priority - so a high might be 3 hours, med 3 business days, etc. When a ticket is moved to closed status, a trigger fires and writes the current date into the completed date field. If a ticket is moved out of completed status, it nulls the completed date.
 
I think it would be a good 'base' feature to have completed tracking baked in. It's not a big deal to add being very extensible, but I'm guessing alot of folks don't use BT because of difficulty in tracking completions.
 
My trigger I use is:
 
USE [btnet]
GO
 
/ Object: Trigger [UpdateCompletedDate] Script Date: 02/26/2012 11:26:51 /
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[UpdateCompletedDate]'))
DROP TRIGGER [dbo].[UpdateCompletedDate]
GO
 
USE [btnet]
GO
 
/ Object: Trigger [dbo].[UpdateCompletedDate] Script Date: 02/26/2012 11:26:54 /
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
 
 
CREATE TRIGGER [dbo].[UpdateCompletedDate]
ON [dbo].[bugs] 
AFTER INSERT,UPDATE
AS
 
IF UPDATE(bg_status)
BEGIN
 
declare @status as integer 
select @status=bg_status from inserted 
declare @id as integer 
select @id=bg_id from inserted 
declare @currentcomplete as datetime
select @currentcomplete = [completed date] from inserted 

/* look for completed updates only where date is currently null - don't update again if populated */ 
IF @status in (5,14)
    IF @currentcomplete is null
        BEGIN 
        /* updates the completed date field if status changes to complete */ 
        update b 
        set [completed date] = GETDATE() 
        from dbo.bugs b 
        inner join inserted  i on i.bg_id = b.bg_id 
 
        insert into bug_posts 
        (bp_bug, bp_user, bp_date, bp_comment, bp_type) 
        values(@id, 21, getdate(), N'Auto SLA: set completed date to ' + convert(varchar, getdate(), 9)  + ' on status complete update', 'update') 
        END 
    /* for debugging when updates occur */
    ELSE
        insert into bug_posts 
        (bp_bug, bp_user, bp_date, bp_comment, bp_type) 
        values(@id, 21, getdate(), N'Auto SLA: left completed date unchanged due to prior completion', 'update') 
 
ELSE 
    /* only do something if completed currently populated and status is not sla tracked
    if completed is null already, don't do anything */
        IF @currentcomplete is not null
            BEGIN     
                /* removes the timestamp if goes out of completed status */ 
                update b 
                set [completed date] = null 
                from dbo.bugs b 
                inner join inserted i on i.bg_id = b.bg_id 

                declare @msg varchar(200)
                SELECT @msg = N'Auto SLA: previous completed date cleared to null due to non complete status change.  '
                              + 'Previous Completed Date was : ' + convert(varchar(20),@currentcomplete)

                insert into bug_posts 
                (bp_bug, bp_user, bp_date, bp_comment, bp_type) 
                values(@id, 21, getdate(), @msg , 'update') 
            END
        /*ELSE
             for debug - don't write to log if nothing to change
            insert into bug_posts 
            (bp_bug, bp_user, bp_date, bp_comment, bp_type) 
            values(@id, 21, getdate(), N'Auto SLA: completed date not modified - ticket is not completed', 'update')                
             */
END
 
 
 
 
 
GO

comments

jsaville wrote Feb 26, 2012 at 4:34 PM

BTW - happy to see you on codeplex! We love BT, and have been using it thru many versions...

I should add we are running on SQL 2008R2 (for the above script - have not tested on previous SQL versions). I think it would be better to do this in code, not database triggers.

wrote Jun 8, 2012 at 8:12 PM

wrote Feb 14, 2013 at 7:11 PM