Report SQL Assistance (HD_WORK)
Hello All,
I have a report that lists all open project tickets. My management team would like to include work notes and work hours in this report. From what I understand, you can only use one table in the reporting wizard and that's my dilemma. I assume that I will have to incorporate the HD_WORK table information manually and this is where I need some assistance.
The following is the SQL for my current report:
SELECT HD_TICKET.CREATED AS HD_TICKET_CREATED, HD_TICKET.DUE_DATE AS HD_TICKET_DUE_DATE, O.FULL_NAME AS OWNER_NAME, HD_STATUS.NAME AS STATUS, S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.ID AS HD_TICKET_ID, HD_TICKET.TITLE AS HD_TICKET_TITLE, HD_TICKET.CUSTOM_FIELD_VALUE0 AS HD_TICKET_CUSTOM_FIELD_VALUE0, HD_TICKET.CUSTOM_FIELD_VALUE1 AS HD_TICKET_CUSTOM_FIELD_VALUE1, HD_TICKET.CUSTOM_FIELD_VALUE2 AS HD_TICKET_CUSTOM_FIELD_VALUE2, HD_TICKET.CUSTOM_FIELD_VALUE3 AS HD_TICKET_CUSTOM_FIELD_VALUE3 FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 25) AND ( HD_STATUS.NAME NOT LIKE '%Closed%') ORDER BY HD_TICKET.DUE_DATE asc,O.FULL_NAME asc,HD_TICKET.ID asc,HD_TICKET.CUSTOM_FIELD_VALUE3 asc
How can I incorporate the work notes and work hours for each project into this report? What would be the SQL needed for this to happen? Any assistance would be greatly appreciated!
Thanks in advance.
Pam
I have a report that lists all open project tickets. My management team would like to include work notes and work hours in this report. From what I understand, you can only use one table in the reporting wizard and that's my dilemma. I assume that I will have to incorporate the HD_WORK table information manually and this is where I need some assistance.
The following is the SQL for my current report:
SELECT HD_TICKET.CREATED AS HD_TICKET_CREATED, HD_TICKET.DUE_DATE AS HD_TICKET_DUE_DATE, O.FULL_NAME AS OWNER_NAME, HD_STATUS.NAME AS STATUS, S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.ID AS HD_TICKET_ID, HD_TICKET.TITLE AS HD_TICKET_TITLE, HD_TICKET.CUSTOM_FIELD_VALUE0 AS HD_TICKET_CUSTOM_FIELD_VALUE0, HD_TICKET.CUSTOM_FIELD_VALUE1 AS HD_TICKET_CUSTOM_FIELD_VALUE1, HD_TICKET.CUSTOM_FIELD_VALUE2 AS HD_TICKET_CUSTOM_FIELD_VALUE2, HD_TICKET.CUSTOM_FIELD_VALUE3 AS HD_TICKET_CUSTOM_FIELD_VALUE3 FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 25) AND ( HD_STATUS.NAME NOT LIKE '%Closed%') ORDER BY HD_TICKET.DUE_DATE asc,O.FULL_NAME asc,HD_TICKET.ID asc,HD_TICKET.CUSTOM_FIELD_VALUE3 asc
How can I incorporate the work notes and work hours for each project into this report? What would be the SQL needed for this to happen? Any assistance would be greatly appreciated!
Thanks in advance.
Pam
0 Comments
[ + ] Show comments
Answers (10)
Please log in to answer
Posted by:
GillySpy
13 years ago
I'll infer that you are not using start time and stop time at all then. Here is your query with a couple of tweaks marked with /****/
SELECT HD_TICKET.ID, HD_TICKET.CREATED AS HD_TICKET_CREATED, HD_TICKET.DUE_DATE AS HD_TICKET_DUE_DATE, O.FULL_NAME AS OWNER_NAME, HD_STATUS.NAME AS STATUS, S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.ID AS HD_TICKET_ID, HD_TICKET.TITLE AS HD_TICKET_TITLE, HD_TICKET.CUSTOM_FIELD_VALUE0 AS HD_TICKET_CUSTOM_FIELD_VALUE0, HD_TICKET.CUSTOM_FIELD_VALUE1 AS HD_TICKET_CUSTOM_FIELD_VALUE1, HD_TICKET.CUSTOM_FIELD_VALUE2 AS HD_TICKET_CUSTOM_FIELD_VALUE2, HD_TICKET.CUSTOM_FIELD_VALUE3 AS HD_TICKET_CUSTOM_FIELD_VALUE3
/*****/
, GROUP_CONCAT(CONCAT(CAST(W.MODIFIED as CHAR),'\n------\n',W.NOTE) SEPARATOR '\n\n') NOTES,
SUM(ADJUSTMENT_HOURS)
/*****/
FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
/********/
LEFT JOIN HD_WORK W ON W.HD_TICKET_ID=HD_TICKET.ID
/********/
WHERE HD_TICKET.HD_QUEUE_ID =25 and HD_STATUS.NAME NOT LIKE '%Closed%'
/********/ GROUP BY HD_TICKET.ID /********/
ORDER BY HD_TICKET.DUE_DATE asc,O.FULL_NAME asc,HD_TICKET.ID asc,HD_TICKET.CUSTOM_FIELD_VALUE3 asc
Posted by:
davids
13 years ago
Gilly, thats brilliant.
I've slightly customised it for you Pam so it lists the fields in the order you specified above.
- Dave
I've slightly customised it for you Pam so it lists the fields in the order you specified above.
SELECT HD_TICKET.CREATED AS Created,
HD_TICKET.ID AS ID,
S.FULL_NAME AS Submitter,
HD_TICKET.TITLE AS Title,
HD_TICKET.CUSTOM_FIELD_VALUE0 AS Scope,
O.FULL_NAME AS Owner,
HD_TICKET.CUSTOM_FIELD_VALUE2 AS 'Additonal Staff',
HD_TICKET.CUSTOM_FIELD_VALUE1 AS Stakeholder,
HD_TICKET.DUE_DATE AS 'Due Date',
HD_TICKET.CUSTOM_FIELD_VALUE3 AS Region,
HD_STATUS.NAME AS Status,
GROUP_CONCAT(CONCAT(CAST(W.MODIFIED as CHAR),'\n - \n',W.NOTE) SEPARATOR '\n | \n') 'Work Notes',
TRUNCATE(SUM(ADJUSTMENT_HOURS),2) 'Adjustment Hours'
FROM HD_TICKET
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
LEFT JOIN HD_WORK W ON W.HD_TICKET_ID=HD_TICKET.ID
WHERE HD_TICKET.HD_QUEUE_ID = 25
AND HD_STATUS.NAME NOT LIKE '%Closed%'
GROUP BY HD_TICKET.ID
ORDER BY HD_TICKET.DUE_DATE ASC,O.FULL_NAME ASC,HD_TICKET.ID ASC,HD_TICKET.CUSTOM_FIELD_VALUE3 ASC
- Dave
Posted by:
GillySpy
13 years ago
You would have to add in the column names for the work field and add up any entries like "total work time" or something. If you mock up an example of a few rows of what you want then I (or maybe someone else reading) will take a crack at it.
Should be fairly simple.
Hours worked on a particular entry is:
So total hours worked is the sum of that when grouping by the Ticket id (HD_WORK.HD_TICKET_ID)
e.g. a more complex query i did recently for a customer:
Should be fairly simple.
Hours worked on a particular entry is:
ROUND((time_to_sec(time(stop)) - time_to_sec(time(start)))/3600.0 + ADJUSTMENT_HOURS, 2)So total hours worked is the sum of that when grouping by the Ticket id (HD_WORK.HD_TICKET_ID)
e.g. a more complex query i did recently for a customer:
select W.STOP as DATE, CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET,
U.FULL_NAME, substring(W.NOTE,1,1200) as NOTE,
ROUND((time_to_sec(time(stop)) - time_to_sec(time(start)))/3600.0 + ADJUSTMENT_HOURS, 2) as HOURS_WORKED,
CONCAT(CAST(T.ID AS CHAR), " - ", RPAD(T.TITLE,30,' -'), ' Total Hours Worked: ',TOTAL_HOURS_WORKED) as TITLE,
TOTAL_HOURS_WORKED
from (HD_WORK W, HD_TICKET T, USER U)
JOIN (select T.ID,SUM(
ROUND((time_to_sec(time(stop)) - time_to_sec(time(start)))/3600.0 + ADJUSTMENT_HOURS, 2)) as TOTAL_HOURS_WORKED
from (HD_WORK W, HD_TICKET T, USER U)
where W.HD_TICKET_ID = T.ID
and isnull(W.VOIDED_BY)
and W.USER_ID = U.ID
and W.STOP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY T.ID) TOTAL ON TOTAL.ID=T.ID
where W.HD_TICKET_ID = T.ID
and isnull(W.VOIDED_BY)
and W.USER_ID = U.ID
and W.STOP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
ORDER BY T.ID DESC, W.MODIFIED
Posted by:
GillySpy
13 years ago
a ticket can have 0-many work entries so you use a left join to it.
...
FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
LEFT JOIN HD_WORK W ON HD_TICKET.ID=W.HD_TICKET_ID ...
Posted by:
LamBam
13 years ago
Thansk GillySpy. I added the "LEFT JOIN HD_WORK W ON HD_TICKET.ID=W.HD_TICKET_ID" to the SQL statement and it gives me a report with multiple lines for the projects that have work notes. However, it doesn't display any of the work notes or hours. I'm new to SQL, so I'm not sure if I need to add additional information to obtain the results needed. I truly appreciate your help!
Posted by:
davids
13 years ago
Hi LamBam,
To add the extra columns you need to specify which fields you want shown in the SELECT area of the query.
What you want to do is add the following between .....M_FIELD_VALUE3 AS HD_TICKET_CUSTOM_FIELD_VALUE3 ... and ... from HD_TICKET LEFT JO.....:
, HD_WORK.NOTE AS WORK_NOTE, HD_WORK.ADJUSTMENT_HOURS AS WORK_HOURS
Notice the , at the start of the additional fields. You need to separate each separate field that you want to select with a , unless it is the last field you are selecting.
This presumes that you are using adjustment hours in your work instead of using start and stop date/time.
If you are using the Start and Stop date/time with work, you would want to replace 'HD_WORK.ADJUSTMENT_HOURS' with 'TIMESTAMPDIFF(HOUR,HD_WORK.START,HD_WORK.STOP)'
Hope this helps mate
-Dave
To add the extra columns you need to specify which fields you want shown in the SELECT area of the query.
What you want to do is add the following between .....M_FIELD_VALUE3 AS HD_TICKET_CUSTOM_FIELD_VALUE3 ... and ... from HD_TICKET LEFT JO.....:
, HD_WORK.NOTE AS WORK_NOTE, HD_WORK.ADJUSTMENT_HOURS AS WORK_HOURS
Notice the , at the start of the additional fields. You need to separate each separate field that you want to select with a , unless it is the last field you are selecting.
This presumes that you are using adjustment hours in your work instead of using start and stop date/time.
If you are using the Start and Stop date/time with work, you would want to replace 'HD_WORK.ADJUSTMENT_HOURS' with 'TIMESTAMPDIFF(HOUR,HD_WORK.START,HD_WORK.STOP)'
Hope this helps mate
-Dave
Posted by:
LamBam
13 years ago
Hi Dave, thanks so much for the additional column information and the instructions on SQL. Very insightful [:)]
When I apply the statements, I get the following error
" mysql error: [1054: Unknown column 'HD_WORK.NOTE' in 'field list'] in EXECUTE(
"SELECT HD_TICKET.CREATED AS Date, HD_TICKET.DUE_DATE AS DueDate, O.FULL_NAME AS Lead, HD_STATUS.NAME AS Status, S.FULL_NAME AS Requester, HD_TICKET.ID AS ProjectID, HD_TICKET.TITLE AS Description, HD_TICKET.CUSTOM_FIELD_VALUE0 AS Scope, HD_TICKET.CUSTOM_FIELD_VALUE1 AS Stakeholder, HD_TICKET.CUSTOM_FIELD_VALUE2 AS AdditionalStaff, HD_TICKET.CUSTOM_FIELD_VALUE3 AS Region, HD_WORK.NOTE AS TasksAccomplished, HD_WORK.ADJUSTMENT_HOURS AS TimeSpent FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN HD_WORK W ON HD_TICKET.ID=W.HD_TICKET_ID WHERE (HD_TICKET.HD_QUEUE_ID = 25) AND ( HD_STATUS.NAME NOT LIKE '%Closed%') ORDER BY HD_TICKET.CUSTOM_FIELD_VALUE3 asc,O.FULL_NAME asc, HD_TICKET.ID asc, HD_TICKET.DUE_DATE asc LIMIT 10")"
What am I doing wrong here??? Thanks again!
When I apply the statements, I get the following error
" mysql error: [1054: Unknown column 'HD_WORK.NOTE' in 'field list'] in EXECUTE(
"SELECT HD_TICKET.CREATED AS Date, HD_TICKET.DUE_DATE AS DueDate, O.FULL_NAME AS Lead, HD_STATUS.NAME AS Status, S.FULL_NAME AS Requester, HD_TICKET.ID AS ProjectID, HD_TICKET.TITLE AS Description, HD_TICKET.CUSTOM_FIELD_VALUE0 AS Scope, HD_TICKET.CUSTOM_FIELD_VALUE1 AS Stakeholder, HD_TICKET.CUSTOM_FIELD_VALUE2 AS AdditionalStaff, HD_TICKET.CUSTOM_FIELD_VALUE3 AS Region, HD_WORK.NOTE AS TasksAccomplished, HD_WORK.ADJUSTMENT_HOURS AS TimeSpent FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN HD_WORK W ON HD_TICKET.ID=W.HD_TICKET_ID WHERE (HD_TICKET.HD_QUEUE_ID = 25) AND ( HD_STATUS.NAME NOT LIKE '%Closed%') ORDER BY HD_TICKET.CUSTOM_FIELD_VALUE3 asc,O.FULL_NAME asc, HD_TICKET.ID asc, HD_TICKET.DUE_DATE asc LIMIT 10")"
What am I doing wrong here??? Thanks again!
Posted by:
LamBam
13 years ago
Hi GillySpy, this report looks awesome! Now, if I could only apply this report to my project tickets (with a few minor adjustments), I will be on my way. [:)]
Basically, I am looking to capture the following ticket information:
*Create Date
*ID
*Submitter
*Title
*Scope (Custom field value0)
*Owner
*Additional Staff (Custom field value2)
*Stakeholder (Custom field value1)
*Due Date
*Region (Custom field value3)
*Status
*Work Notes
*Adjustment Hours
So far, it has been a challenge as I cannot seem to retrieve the HD_Work information. You guys are great.... Any assistance with this is greatly appreciated!!
Pam
Basically, I am looking to capture the following ticket information:
*Create Date
*ID
*Submitter
*Title
*Scope (Custom field value0)
*Owner
*Additional Staff (Custom field value2)
*Stakeholder (Custom field value1)
*Due Date
*Region (Custom field value3)
*Status
*Work Notes
*Adjustment Hours
So far, it has been a challenge as I cannot seem to retrieve the HD_Work information. You guys are great.... Any assistance with this is greatly appreciated!!
Pam
Posted by:
LamBam
13 years ago
YES.. this is absolutely fabulous!! You guys are simply AWESOME!!!! Thanks, thanks, thanks so much for your help with this.... it is exactly what I need!! Perfect! [:D]
The reports are great and your assistance really helped me to understand SQL a lot better. I am starting to gain more knowledge in how to manipulate the outcome of the SQL reports... all thanks to you. I love it!!
Thanks again guys and have a great week!
Pam
The reports are great and your assistance really helped me to understand SQL a lot better. I am starting to gain more knowledge in how to manipulate the outcome of the SQL reports... all thanks to you. I love it!!
Thanks again guys and have a great week!
Pam
Posted by:
rayjacobs
12 years ago
Gerald,
I took the statement you posted and was wondering how to get all of the tickets to display even if a ticket doesn't have work. I know there needs to be an outer join, but I don't know the correct syntax. Can you add that to this statement you created:
select W.STOP as DATE, CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET,
U.FULL_NAME, substring(W.NOTE,1,1200) as NOTE,
ROUND((time_to_sec(time(stop)) - time_to_sec(time(start)))/3600.0 + ADJUSTMENT_HOURS, 2) as HOURS_WORKED,
CONCAT(CAST(T.ID AS CHAR), " - ", RPAD(T.TITLE,30,' -'), ' Total Hours Worked: ',TOTAL_HOURS_WORKED) as TITLE,
TOTAL_HOURS_WORKED
from (HD_WORK W, HD_TICKET T, USER U)
JOIN (select T.ID,SUM(
ROUND((time_to_sec(time(stop)) - time_to_sec(time(start)))/3600.0 + ADJUSTMENT_HOURS, 2)) as TOTAL_HOURS_WORKED
from (HD_WORK W, HD_TICKET T, USER U)
where W.HD_TICKET_ID = T.ID
and isnull(W.VOIDED_BY)
and W.USER_ID = U.ID
and W.STOP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY T.ID) TOTAL ON TOTAL.ID=T.ID
where W.HD_TICKET_ID = T.ID
and isnull(W.VOIDED_BY)
and W.USER_ID = U.ID
and W.STOP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
ORDER BY T.ID DESC, W.MODIFIED
I took the statement you posted and was wondering how to get all of the tickets to display even if a ticket doesn't have work. I know there needs to be an outer join, but I don't know the correct syntax. Can you add that to this statement you created:
select W.STOP as DATE, CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET,
U.FULL_NAME, substring(W.NOTE,1,1200) as NOTE,
ROUND((time_to_sec(time(stop)) - time_to_sec(time(start)))/3600.0 + ADJUSTMENT_HOURS, 2) as HOURS_WORKED,
CONCAT(CAST(T.ID AS CHAR), " - ", RPAD(T.TITLE,30,' -'), ' Total Hours Worked: ',TOTAL_HOURS_WORKED) as TITLE,
TOTAL_HOURS_WORKED
from (HD_WORK W, HD_TICKET T, USER U)
JOIN (select T.ID,SUM(
ROUND((time_to_sec(time(stop)) - time_to_sec(time(start)))/3600.0 + ADJUSTMENT_HOURS, 2)) as TOTAL_HOURS_WORKED
from (HD_WORK W, HD_TICKET T, USER U)
where W.HD_TICKET_ID = T.ID
and isnull(W.VOIDED_BY)
and W.USER_ID = U.ID
and W.STOP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY T.ID) TOTAL ON TOTAL.ID=T.ID
where W.HD_TICKET_ID = T.ID
and isnull(W.VOIDED_BY)
and W.USER_ID = U.ID
and W.STOP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
ORDER BY T.ID DESC, W.MODIFIED
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.