Hi,

I'm trying to create a report which shows Closed Tickets last 7 days by Owner including the budget code, the reason I'm using the budget code is it's the only way I can think of getting the office of the submitter to appear in the report. I've had a go at modifying the following and adding BUDGET_CODE but I haven't been able to get it to work. Any help would be really appreciated....

select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE ,
DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') as TIME_CLOSED,
CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_TO_CLOSE,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

2
Mewburn,

Try the following:
SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.DUE_DATE,
Date_format(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') AS
TIME_CLOSED,
Concat(IF(Time_to_sec(HD_TICKET.TIME_CLOSED) >= Time_to_sec(
HD_TICKET.TIME_OPENED),
To_days(HD_TICKET.TIME_CLOSED) - To_days(
HD_TICKET.TIME_OPENED),
To_days(
HD_TICKET.TIME_CLOSED) - To_days(HD_TICKET.TIME_OPENED) - 1
), 'd '
, Date_format(Addtime("2000-01-01 00:00:00",
Sec_to_time(Time_to_sec(
HD_TICKET.TIME_CLOSED) -
Time_to_sec(
HD_TICKET.TIME_OPENED))),
'%kh %im'))
AS
TIME_TO_CLOSE,
HD_PRIORITY.NAME AS PRIORITY,
HD_CATEGORY.NAME AS CATEGORY,
HD_STATUS.NAME AS STATUS,
HD_IMPACT.NAME AS IMPACT,
MACHINE.NAME AS
MACHINE_NAME,
Ifnull((SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.OWNER_ID = USER.ID), ' Unassigned') AS
OWNER_NAME,
(SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.SUBMITTER_ID = USER.ID) AS
SUBMITTER_NAME,
(SELECT BUDGET_CODE
FROM USER
WHERE HD_TICKET.SUBMITTER_ID = USER.ID) AS
BUDGET_CODE
FROM HD_TICKET
LEFT JOIN HD_CATEGORY
ON HD_CATEGORY_ID = HD_CATEGORY.ID
LEFT JOIN HD_STATUS
ON HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN HD_PRIORITY
ON HD_PRIORITY_ID = HD_PRIORITY.ID
LEFT JOIN HD_IMPACT
ON HD_IMPACT_ID = HD_IMPACT.ID
LEFT JOIN MACHINE
ON HD_TICKET.MACHINE_ID = MACHINE.ID
WHERE HD_STATUS.STATE = 'closed'
AND HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY OWNER_NAME,
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL

Hope it works for you.
Answered 01/07/2011 by: dchristian
Red Belt

Please log in to comment
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Hi David,

That's brilliant, thanks for your help.

I've submitted the report to my manager and he's now asked if the report can be ordered/grouped by budget code and that there is a subtotal for each budget code at the bottom of the grouping. He basically wants to see how many tickets are being reported from each office? He could just count them up but that would be too much work ;-) and I've been told to order a MYSQL book too, the joy!

Thanks
Answered 01/10/2011 by: mewburn
Senior Yellow Belt

Please log in to comment
2
mewburn,

I added a BUDGET_CODE as the new sort criteria.

When creating the report in the K1000 there is a break on columns box.

Set this box to BUDGET_CODE and you will get the grouping and subtotal effect.


SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.DUE_DATE,
Date_format(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') AS TIME_CLOSED,
Concat(IF(Time_to_sec(HD_TICKET.TIME_CLOSED) >= Time_to_sec(HD_TICKET.TIME_OPENED), To_days(HD_TICKET.TIME_CLOSED) - To_days(HD_TICKET.TIME_OPENED), To_days(HD_TICKET.TIME_CLOSED) - To_days(HD_TICKET.TIME_OPENED) - 1), 'd ', Date_format(Addtime("2000-01-01 00:00:00", Sec_to_time(Time_to_sec(HD_TICKET.TIME_CLOSED) - Time_to_sec(HD_TICKET.TIME_OPENED))), '%kh %im')) AS TIME_TO_CLOSE,
HD_PRIORITY.NAME AS PRIORITY,
HD_CATEGORY.NAME AS CATEGORY,
HD_STATUS.NAME AS STATUS,
HD_IMPACT.NAME AS IMPACT,
MACHINE.NAME AS MACHINE_NAME,
Ifnull((SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.OWNER_ID = USER.ID), ' Unassigned') AS OWNER_NAME,
(SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.SUBMITTER_ID = USER.ID) AS SUBMITTER_NAME,
(SELECT BUDGET_CODE
FROM USER
WHERE HD_TICKET.SUBMITTER_ID = USER.ID) AS BUDGET_CODE
FROM HD_TICKET
LEFT JOIN HD_CATEGORY
ON HD_CATEGORY_ID = HD_CATEGORY.ID
LEFT JOIN HD_STATUS
ON HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN HD_PRIORITY
ON HD_PRIORITY_ID = HD_PRIORITY.ID
LEFT JOIN HD_IMPACT
ON HD_IMPACT_ID = HD_IMPACT.ID
LEFT JOIN MACHINE
ON HD_TICKET.MACHINE_ID = MACHINE.ID
WHERE HD_STATUS.STATE = 'closed'
AND HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY BUDGET_CODE,
OWNER_NAME,
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL

Hope this helps
Answered 01/10/2011 by: dchristian
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity