How do you create a K1000 report that list comments/work on notes made the day prior sorted by owner?
I have a custom report (below) and would like to add the most recent comment and/or work on notes to it. Could anyone help me edit this SQL statement?
Select
HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.CUSTOM_FIELD_VALUE2 As `Ticket Type`,
Concat(If(Time_To_Sec(Now()) >= Time_To_Sec(HD_TICKET.TIME_OPENED),
To_Days(Now()) - To_Days(HD_TICKET.TIME_OPENED), To_Days(Now()) -
To_Days(HD_TICKET.TIME_OPENED) - 1), 'd ',
Date_Format(AddTime("2000-01-01 00:00:00", Sec_To_Time(Time_To_Sec(Now()) -
Time_To_Sec(HD_TICKET.TIME_OPENED))), '%kh %im')) As TIME_OPEN,
HD_TICKET.MODIFIED As `Last Updated`,
HD_TICKET.DUE_DATE,
HD_CATEGORY.NAME As CATEGORY,
HD_STATUS.NAME As STATUS,
MACHINE.NAME As MACHINE_NAME,
IfNull((Select
USER.FULL_NAME
From
USER
Where
HD_TICKET.OWNER_ID = USER.ID), ' Unassigned') As OWNER_NAME,
(Select
USER.FULL_NAME
From
USER
Where
HD_TICKET.SUBMITTER_ID = USER.ID) As SUBMITTER_NAME
From
HD_TICKET Left Join
HD_CATEGORY On HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID Left Join
HD_STATUS On HD_TICKET.HD_STATUS_ID = HD_STATUS.ID Left Join
HD_PRIORITY On HD_TICKET.HD_PRIORITY_ID = HD_PRIORITY.ID Left Join
HD_IMPACT On HD_TICKET.HD_IMPACT_ID = HD_IMPACT.ID Left Join
MACHINE On HD_TICKET.MACHINE_ID = MACHINE.ID
Where
(HD_STATUS.STATE = 'stalled') Or
(HD_STATUS.STATE = 'opened')
Order By
OWNER_NAME,
`Last Updated`,
TIME_OPEN Desc,
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
Vfrancois
10 years ago
I have used the following:
For the actual note, I didn't want the whole note because they could be too long so you could leave of fhte Cast if you wanted to:
CAST(HD_WORK.NOTE AS CHAR(50)) AS 'WORK NOTE ADDED'
and this had to be added in the joins:
LEFT JOIN HD_WORK ON HD_WORK.HD_TICKET_ID=HD_TICKET.ID
