/build/static/layout/Breadcrumb_cap_w.png

KACE Report Showing Queue, Project, and Sum of Worked Hours per Day of Prior Work Week

Hello, I need assistance creating a report in KACE. I'm familiar with SQL queries at best, and have tried to create the report using similar reports as examples, but nothing gets me exactly what I need. I have to enter time for each department for which I performed work. When entering time I have to choose the project associated with that department - so "break-fix", or a special project like "phone system upgrade." At the end of the week I have to take that time and manually enter it into another ticketing system, which is the reason I need the report. The closer I can get the KACE report in the same format as the second ticketing system, the easier, and more accurately, I can manually transfer the time. The other ticketing system requires the following format:

Department, Project, Total Hours Spent on Monday on This Project, Total Hours Spent on Tuesday on This Project, Total Hours Spent on Wednesday on This Project, Total Hours Spent on Thursday on This Project, Total Hours Spent on Friday on This Project

My hope is to create a report in this format:
Manufacturing, Breakfix, 2, 3, 1, 2.25, 1.5
Manufacturing, Phone System Upgrade, 0, 0, 4, 0, 2.25
Finance, Breakfix, 0, 1, 0, 0, 0
etc.

The following gets me more than I need (like the ticket number, description, and status), and exporting to a CSV allows me to at least manipulate the data, but even in Excel I have to do a bit of work to get it into the needed format.

SELECT
HD_QUEUE.NAME AS 'Queue',
HD_IMPACT.NAME AS 'Project',
HD_TICKET.ID AS 'Ticket Number',
HD_TICKET.TITLE AS 'Request',
HD_STATUS.NAME AS 'Status',
SUBMITTER.FULL_NAME AS 'Customer',
HD_WORK.NOTE AS 'Work Description',
USER.FULL_NAME AS 'Engineer',
(ROUND(ADJUSTMENT_HOURS*4, 0)/4) as 'Hours Worked',
DATE(HD_WORK.STOP) As 'Work Date'

FROM HD_TICKET
LEFT JOIN HD_IMPACT ON HD_TICKET.HD_IMPACT_ID=HD_IMPACT.ID
LEFT JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
LEFT JOIN HD_WORK ON HD_TICKET.ID=HD_WORK.HD_TICKET_ID
LEFT JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID=HD_QUEUE.ID
LEFT JOIN USER ON HD_WORK.USER_ID=USER.ID
LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

WHERE
HD_QUEUE.ID is not null
AND HD_WORK.MODIFIED BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 1 WEEK ) AND DATE_SUB( CURDATE( ) ,INTERVAL 0 WEEK )
AND HD_WORK.VOIDED_BY IS NULL
AND USER.FULL_NAME LIKE 'lloydgm'

I need the prior work week, which the above query outputs, but I'll also need the prior month for an end of the month report.

I've worked on this for a couple days and I've hit a point that I need some help. Any assistance is greatly appreciated!
Asked 7 years ago 1372 views
1 Comment   [ + ] Show comment
  • Can you post the excel formula(s)? Might help us figure out the corresponding SQL logic. - JasonEgg 7 years ago

Answers (2)

Answer Summary:
Posted by: chucksteel 7 years ago
Red Belt
3

Top Answer

How does this look?
SELECT 
HD_QUEUE.NAME AS 'Queue',
HD_IMPACT.NAME AS 'Project',
HD_TICKET.ID AS 'Ticket Number',
HD_TICKET.TITLE AS 'Request',
HD_STATUS.NAME AS 'Status',
SUBMITTER.FULL_NAME AS 'Customer',
HD_WORK.NOTE AS 'Work Description',
USER.FULL_NAME AS 'Engineer',
(SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 2 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
 as Monday,
(SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 3 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
 as Tuesday,
(SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 4 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
 as Wednesday,
(SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 5 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
 as Thursday,
(SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 6 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
 as Friday
FROM HD_TICKET 
LEFT JOIN HD_IMPACT ON HD_TICKET.HD_IMPACT_ID=HD_IMPACT.ID
LEFT JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
LEFT JOIN HD_WORK ON HD_TICKET.ID=HD_WORK.HD_TICKET_ID
LEFT JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID=HD_QUEUE.ID
LEFT JOIN USER ON HD_WORK.USER_ID=USER.ID
LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
WHERE 
HD_QUEUE.ID is not null
and ADJUSTMENT_HOURS > 0
GROUP BY HD_TICKET.ID
This groups by ticket which might not be exactly what you want. Your description says department and project and those are the queue and impact fields. If you need the work summed on those fields things get even more complicated.

We don't generally use the adjustment hours fields so I didn't limit my query to the past week in order to include what test data I do have in the database. You should be able to add those restrictions back into your query.
Comments:
  • This is great, Chuck! Thanks for the time and work. I made two changes, specifying the exact engineer (me in this case) and the past week (it was showing all tickets entered since 2012). The day columns are perfect. The only difficulty I'm having now is that all tickets from all projects are shown, where I need a summary of time for each queue and project, which would ultimately make for a smaller list.

    For instance:
    Queue: Accounting, Project: Break-Fix, Monday: all time logged I project "break-fix" for Monday, all time logged I project "break-fix" for Tuesday, all time logged I project "break-fix" for Wednesday, etc.

    Here's the updated script.

    SELECT
    HD_QUEUE.NAME AS 'Queue',
    HD_IMPACT.NAME AS 'Project',
    HD_TICKET.ID AS 'Ticket Number',
    HD_TICKET.TITLE AS 'Request',
    HD_STATUS.NAME AS 'Status',
    SUBMITTER.FULL_NAME AS 'Customer',
    HD_WORK.NOTE AS 'Work Description',
    USER.FULL_NAME AS 'Engineer',
    (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 2 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
    as Monday,
    (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 3 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
    as Tuesday,
    (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 4 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
    as Wednesday,
    (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 5 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
    as Thursday,
    (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 6 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
    as Friday
    FROM HD_TICKET
    LEFT JOIN HD_IMPACT ON HD_TICKET.HD_IMPACT_ID=HD_IMPACT.ID
    LEFT JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
    LEFT JOIN HD_WORK ON HD_TICKET.ID=HD_WORK.HD_TICKET_ID
    LEFT JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID=HD_QUEUE.ID
    LEFT JOIN USER ON HD_WORK.USER_ID=USER.ID
    LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
    WHERE
    HD_QUEUE.ID is not null
    AND HD_WORK.MODIFIED BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 1 WEEK ) AND DATE_SUB( CURDATE( ) ,INTERVAL 0 WEEK )
    AND USER.FULL_NAME LIKE 'lloydgm'
    AND ADJUSTMENT_HOURS > 0
    GROUP BY HD_TICKET.ID

    Thanks again. This is seriously a huge help. - lloydgm 7 years ago
  • This is great, Chuck! Thanks for the time and work. I made two changes, specifying the exact engineer (me in this case) and the past week (it was showing all tickets entered since 2012). The day columns are perfect. The only difficulty I'm having now is that all tickets from all projects are shown, where I need a summary of time for each queue and project, which would ultimately make for a smaller list.

    For instance:
    Queue: Accounting, Project: Break-Fix, Monday: all time logged I project "break-fix" for Monday, all time logged I project "break-fix" for Tuesday, all time logged I project "break-fix" for Wednesday, etc.

    Here's the updated script.

    SELECT
    HD_QUEUE.NAME AS 'Queue',
    HD_IMPACT.NAME AS 'Project',
    HD_TICKET.ID AS 'Ticket Number',
    HD_TICKET.TITLE AS 'Request',
    HD_STATUS.NAME AS 'Status',
    SUBMITTER.FULL_NAME AS 'Customer',
    HD_WORK.NOTE AS 'Work Description',
    USER.FULL_NAME AS 'Engineer',
    (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 2 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
    as Monday,
    (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 3 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
    as Tuesday,
    (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 4 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
    as Wednesday,
    (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 5 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
    as Thursday,
    (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 6 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
    as Friday
    FROM HD_TICKET
    LEFT JOIN HD_IMPACT ON HD_TICKET.HD_IMPACT_ID=HD_IMPACT.ID
    LEFT JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
    LEFT JOIN HD_WORK ON HD_TICKET.ID=HD_WORK.HD_TICKET_ID
    LEFT JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID=HD_QUEUE.ID
    LEFT JOIN USER ON HD_WORK.USER_ID=USER.ID
    LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
    WHERE
    HD_QUEUE.ID is not null
    AND HD_WORK.MODIFIED BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 1 WEEK ) AND DATE_SUB( CURDATE( ) ,INTERVAL 0 WEEK )
    AND USER.FULL_NAME LIKE 'lloydgm'
    AND ADJUSTMENT_HOURS > 0
    GROUP BY HD_TICKET.ID

    Thanks so much for the time you've already put into this. This is seriously a huge help. - lloydgm 7 years ago
    • I wrapped my original query as a subselect and grouped by queue and project:
      SELECT QUEUE, PROJECT, SUM(Monday), SUM(Tuesday), SUM(Wednesday), SUM(Thursday), SUM(Friday) FROM (
      SELECT
      HD_QUEUE.NAME AS 'Queue',
      HD_IMPACT.NAME AS 'Project',
      (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 2 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID
      and WEEK(STOP) = WEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK)))
      as Monday,
      (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 3 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID
      and WEEK(STOP) = WEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK)))
      as Tuesday,
      (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 4 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID
      and WEEK(STOP) = WEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK)))
      as Wednesday,
      (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 5 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID
      and WEEK(STOP) = WEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK)))
      as Thursday,
      (SELECT SUM(ADJUSTMENT_HOURS) FROM HD_WORK WHERE DAYOFWEEK(STOP) = 6 and HD_WORK.HD_TICKET_ID = HD_TICKET.ID
      and WEEK(STOP) = WEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK)))
      as Friday

      FROM HD_TICKET
      LEFT JOIN HD_IMPACT ON HD_TICKET.HD_IMPACT_ID=HD_IMPACT.ID
      LEFT JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
      LEFT JOIN HD_WORK ON HD_TICKET.ID=HD_WORK.HD_TICKET_ID
      LEFT JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID=HD_QUEUE.ID
      LEFT JOIN USER ON HD_WORK.USER_ID=USER.ID
      LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

      WHERE
      HD_QUEUE.ID is not null
      and ADJUSTMENT_HOURS > 0
      GROUP BY HD_TICKET.ID) Tickets
      GROUP BY Queue, Project

      Also, you need to put the condition on work from last week in the sub select statements per day. I used WEEK(STOP) = WEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK)) to get work from the previous week. This way it is tracking when the work was done, not when you modified the work entry. I needed to add back data for testing so it wouldn't work for me using the modified date. - chucksteel 7 years ago
Posted by: chucksteel 7 years ago
Red Belt
1
This will get messy but I think that the best option might be a series of subqueries that get the sum of work for each day of the week. 
Comments:
  • Thanks for your reply. My difficulty isn't in the algorithm as such as it is in writing the query. Are you proficient enough with SQL in relation to KACE to write modify the above, or write an example. I hate asking other to do my work for me, I've just hit that point of pulling my hair out. - lloydgm 7 years ago

Posted by:

lloydgm White Belt
Ninja since: 7 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

KACE Product Support Questions
question

Related Questions

Link

Related Links

Post

Related Posts

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ