SQL query - finding actual work time


I am trying to write a SQL query to calculate actual work time for help desk tickets. This should only calculate the time from the ticket Opened time to Resolved time. The clock stops when the ticket reaches "Resolved" status.
Exemptions :
Exclude if a ticket is in "Pending" status.
Exclude if a ticket is assigned to "Vendor Support".

Attached, please find sample data
Any assistance is greatly appreciated
Thank you
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mandeep SinghDatabase AdministratorCommented:
Hi try this:
set @mintime = '2009-02-02 11:12:20'
set @maxtime = '2009-02-02 13:30:25'

declare @duration int
set @duration = datediff([second], @minTime, @maxtime);

		THEN '0' + h
		+ ':' +
		CASE WHEN LEN(m) = 1
		THEN '0' + m
		+ ':' + 
		CASE WHEN LEN(s) = 1
		THEN '0' + s
  CAST(@duration / 3600 AS VARCHAR(3)) as [h],
  CAST(@duration % 3600 / 60 AS VARCHAR(3)) as [m],
  CAST(@duration % 3600 % 60 AS VARCHAR(3))as [s]) AS X

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Your sample data shows the same OpenTime and ResolvedTime for all single entry for a giving Ticket Number so I don't know what you gain in excluding rows.
By the sample you provided I could see that all tickets have only one "OPEN" status so I will use it to guarantee the uniqueness from each ticket. You also didn't specify how do you want the work time to be returned so I'm using hours in my solution:
SELECT Number, DATEDIFF(SECOND,Opentime, ResolvedTime)/60/60.0 AS WorkTimeHours
FROM Ticket
WHERE oldvalue='Open'

Open in new window

angel7170Author Commented:
I should have explained this better. Sorry about that!

A ticket has a open time and resolved time. The difference between these two date fields should exclude when the ticket is in pending status (which is fieldname like 'state') and it should also exclude if it is in assignment_group = "Vendor Support". The activity time is what's shown in "Sys_created_on".

Not sure if this explains better....
thank you
Vitor MontalvãoMSSQL Senior EngineerCommented:
What I meant is that doesn't matter the status the open and resolved time is always the same for the each single ticket so filtering by status is irrelevant. I just filtered by oldvalue='Open' to guarantee the uniqueness of the row since that status appears only once by ticket.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.