Stored Procedure in SQL Server 2008
Posted on 2011-03-22
I'm having trouble in writing a procedure to calculate "Work Time" for each ticket that is resolved in Help Desk environment. This work time should not calculate the time when a ticket is in "Pending" status.
This is the table structure with the data
TicketNo Status Datetime
TK123 TicketOpen 03/22/2011 7:00AM
TK123 To - Pending 03/22/2011 8:00AM
TK123 To - Open 03/22/2011 9:30AM
TK123 To-Pending 03/22/2011 10:00AM
TK123 To-Open 03/22/2011 10:45AM
TK123 To-Closure 03/22/2011 11:30AM
So actual work time should be 2 hours and 15 Minutes excluding Pending Hours ( Time when the ticket went to "To-Pending" and came out to "To-Open" status) which is 1 Hour and 15 Minutes.
How can I write a Procedure to do this calculate? Please assist.
Thank you very much