angel7170
asked on
Stored Procedure in SQL Server 2008
Hello Experts,
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both.
I tried gohord solution and it works as expected. Thanks
I tried gohord solution and it works as expected. Thanks
Open in new window