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

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

Gilly, thats brilliant.

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

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


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

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!

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: 
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


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

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!

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  ...