Closed Tickets last 7 days by Owner incl Budget Code
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
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
Answers (3)
Please log in to answer
Posted by:
dchristian
13 years ago
Mewburn,
Try the following:
Hope it works for you.
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.
Posted by:
dchristian
13 years ago
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.
Hope this helps
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.ORDINALHope this helps
Posted by:
mewburn
13 years ago
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
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
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.