Hi all,

I'm trying to get a report set up that shows a list of all tickets by owner for the last week, including hours worked.  I then want to show a total for each owner so something like:

OWNER

  • Job 1 - Job Title - 2 hours
  • Job 2 - Job Title - 4 hours
  • Job 3 - Job Title - 5 mins

Total for ONWER: 6 hours 5 mins

 

Any ideas on how to achieve this?  SQL is not my strong point and whilst I can get the results back, I can't figure out how to sum the hours worked for each group.

Cheers.  Dave

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

Here's an example report I created to sum up all of the vacation, personal and sick days for each person in my time tracking queue (which I'll be documenting completely and sharing as I have time).  But this should at least let you get started with how to sum the hours and rename the columns to whatever you want (I'm doing both with one statement).  Just for reference, I have three categories (Request::Vacation, Request::Personal Day, Sick Day) and the three SUM lines basically say "if the category is ____, then add up all of the days (kept in T.CUSTOM_FIELD_VALUE6) and call that column _____".  Hopefully enough of an example here to let you get yours working.

_________________________________

*Title*

Time Tracking - Concise Listing

*Category*

Time Tracking (Custom)

*Description*

Concise listing of days off by employee.

*SQL Select Statement*

SELECT T.CUSTOM_FIELD_VALUE0 as EMPLOYEE,

SUM(CASE WHEN C.NAME like '%Vacation%' then T.CUSTOM_FIELD_VALUE6 else 0 END) as VACATION_DAYS,

SUM(CASE WHEN C.NAME like '%Personal%' then T.CUSTOM_FIELD_VALUE6 else 0 END) as PERSONAL_DAYS,

SUM(CASE WHEN C.NAME like '%Sick%' then T.CUSTOM_FIELD_VALUE6 else 0 END) as SICK_DAYS,

T.CUSTOM_FIELD_VALUE1 as DEPARTMENT,

T.CUSTOM_FIELD_VALUE2 as LOCATION, U.FULL_NAME AS MANAGER

FROM HD_TICKET T

JOIN HD_CATEGORY C ON (C.ID = HD_CATEGORY_ID)

JOIN USER U on (U.EMAIL = T.CUSTOM_FIELD_VALUE7)

WHERE T.HD_QUEUE_ID = 4

GROUP BY EMPLOYEE

_________________________________

Hope that helps!

John

Answered 06/25/2012 by: jverbosk
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity