K1000 Service Desk - Time Tracking Queue Addendum
________________________________

For anyone who found the Time Tracking blog useful, here's a few report tweaks that I've developed since my company started using it.  For the original blog, please see:

K1000 Service Desk - Time Tracking Queue (Config, Custom Ticket Rules & SQL Reports)
http://www.itninja.com/blog/view/k1000-service-desk-time-tracking-queue-config-custom-ticket-rules-sql-reports
________________________________

(1&2)
The manager reports go out every Monday morning so managers can track their subordinates' time and approvals, while the full reports go to HR.  

(3&4)
Our CEO requested that he just see his subordinates' time off for the upcoming week, so those reports restrict the detailed report to just the following week and gets sent out Friday mornings.

Another tweak I've made includes adding an Amendment category (with Vacation, Personal Day & Other subcategories) and negative Total Days values (-0.5 ~ -15.0) so that employees can amend previously approved requests so that unused time off won't be counted toward their totals (in the concise listing).  This was suggested by someone in our finance department as a way to provide accountability, as the managers also need to approve/reject the amendment request.
________________________________

1) Concise Listing by Manager

*Title*
Time Tracking - Concise Listing (Joe Smith)

*Category*
Time Tracking (Custom)

*Description*
Concise listing of days off by employee for Joe Smith.

*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,
SUM(CASE WHEN C.NAME like '%Other%' then T.CUSTOM_FIELD_VALUE6 else 0 END) as OTHER,
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
AND T.APPROVAL = 'approved'
AND CUSTOM_FIELD_VALUE7 = 'Joe.Smith@Company.Com'
GROUP BY EMPLOYEE
________________________________

2) Detailed Listing by Manager

*Title*
Time Tracking - Detailed Listing (Joe Smith)

*Category*
Time Tracking (Custom)

*Description*
Detailed listing of days off by employee for Joe Smith.

*SQL Select Statement*
SELECT T.CUSTOM_FIELD_VALUE0 as EMPLOYEE,
REPLACE(REPLACE(C.NAME, 'Request::Personal Day', 'Personal Day'),'Request::Vacation', 'Vacation Day') as CATEGORY,
T.APPROVAL as APPROVAL_STATE, T.CUSTOM_FIELD_VALUE4 as START_DATE, T.CUSTOM_FIELD_VALUE5 as END_DATE,
T.CUSTOM_FIELD_VALUE6 as TOTAL_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
AND CUSTOM_FIELD_VALUE7 = 'Joe.Smith@Company.Com'
ORDER BY EMPLOYEE, CATEGORY, START_DATE

*Break on Columns*
EMPLOYEE
________________________________

3) Weekly Detailed Listing

*Title*
Time Tracking - Weekly Detailed Listing

*Category*
Time Tracking (Custom)

*Description*
Detailed listing of days off by employee for the following week (Sunday - Saturday).

*SQL Select Statement*
SELECT T.CUSTOM_FIELD_VALUE0 as EMPLOYEE,
REPLACE(REPLACE(C.NAME, 'Request::Personal Day', 'Personal Day'),'Request::Vacation', 'Vacation Day') as CATEGORY,
T.APPROVAL as APPROVAL_STATE, T.CUSTOM_FIELD_VALUE4 as START_DATE, T.CUSTOM_FIELD_VALUE5 as END_DATE,
T.CUSTOM_FIELD_VALUE6 as TOTAL_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
AND ((DATEDIFF(STR_TO_DATE(T.CUSTOM_FIELD_VALUE4, '%Y/%m/%d'), NOW()) > 2
AND DATEDIFF(STR_TO_DATE(T.CUSTOM_FIELD_VALUE4, '%Y/%m/%d'), NOW()) < 8)
OR (DATEDIFF(STR_TO_DATE(T.CUSTOM_FIELD_VALUE5, '%Y/%m/%d'), NOW()) > 2
AND DATEDIFF(STR_TO_DATE(T.CUSTOM_FIELD_VALUE5, '%Y/%m/%d'), NOW()) < 8))
ORDER BY EMPLOYEE, CATEGORY, START_DATE

*Break on Columns*
EMPLOYEE
________________________________

4) Weekly Detailed Listing by Manager

*Title*
Time Tracking - Weekly Detailed Listing (Jim Brown)

*Category*
Time Tracking (Custom)

*Description*
Detailed listing of days off by employee for the following week (Sunday - Saturday) for Jim Brown.

*SQL Select Statement*
SELECT T.CUSTOM_FIELD_VALUE0 as EMPLOYEE,
REPLACE(REPLACE(C.NAME, 'Request::Personal Day', 'Personal Day'),'Request::Vacation', 'Vacation Day') as CATEGORY,
T.APPROVAL as APPROVAL_STATE, T.CUSTOM_FIELD_VALUE4 as START_DATE, T.CUSTOM_FIELD_VALUE5 as END_DATE,
T.CUSTOM_FIELD_VALUE6 as TOTAL_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
AND CUSTOM_FIELD_VALUE7 = 'Jim.Brown@Company.Com'
AND ((DATEDIFF(STR_TO_DATE(T.CUSTOM_FIELD_VALUE4, '%Y/%m/%d'), NOW()) > 2
AND DATEDIFF(STR_TO_DATE(T.CUSTOM_FIELD_VALUE4, '%Y/%m/%d'), NOW()) < 8)
OR (DATEDIFF(STR_TO_DATE(T.CUSTOM_FIELD_VALUE5, '%Y/%m/%d'), NOW()) > 2
AND DATEDIFF(STR_TO_DATE(T.CUSTOM_FIELD_VALUE5, '%Y/%m/%d'), NOW()) < 8))
ORDER BY EMPLOYEE, CATEGORY, START_DATE

*Break on Columns*
EMPLOYEE
________________________________

Hope that helps!

John