I have written a Report to show Help Desk Daily Balance but there is a flaw in the coding.

If a user has 10 tickets at the beginning of the day, has 5 assigned and closed 7 then they should have 8 at the end of the day which works fine.

If they have 10 tickets at the beginning of the day, 5 assigned, and close 0 tickets then it shows they have 10 tickets.

Any time a user has 0 assigned and X closed or X closed and 0 assigned the math is always wrong.

Can anyone assist?



SELECT
0 as TECH_ID,
'Unassigned' AS `TECHNICIAN`,
'Unassigned...' AS `EMAIL`,
( SELECT COUNT(HD_TICKET.OWNER_ID)
FROM HD_TICKET
WHERE HD_TICKET.OWNER_ID =0
AND HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 1 DAY)
) AS `BEGINING BALANCE`,
(
SELECT COUNT(HD_TICKET.OWNER_ID)
FROM HD_TICKET
WHERE HD_TICKET.OWNER_ID =0
AND HD_TICKET.CREATED between now()
AND DATE_SUB(NOW(), INTERVAL 2 DAY)
) AS `NEW TICKETS`,
0 AS `CLOSED`,
0 AS `ENDING BALANCE`

UNION

SELECT
IFNULL(U.ID,0) as TECH_ID,
LEFT(U.USER_NAME,30) AS `TECHNICIAN`,
CONCAT(LEFT(U.EMAIL,40)) AS `EMAIL`,
CASE
WHEN OPENTICKETS.`BEGINING BALANCE` IS NULL THEN 0
ELSE OPENTICKETS.`BEGINING BALANCE`
END AS `BEGINING BALANCE`,
CASE
WHEN NEWTICKETS.`NEW TICKETS`IS NULL THEN 0
ELSE NEWTICKETS.`NEW TICKETS`
END AS `NEW TICKETS`,
CASE
WHEN CLOSEDTICKETS.`CLOSED TICKETS`IS NULL THEN 0
ELSE CLOSEDTICKETS.`CLOSED TICKETS`
END AS `CLOSED`,
IFNULL(IFNULL((OPENTICKETS.`BEGINING BALANCE` + NEWTICKETS.`NEW TICKETS`) - CLOSEDTICKETS.`CLOSED TICKETS`,OPENTICKETS.`BEGINING BALANCE`),0) AS `ENDING BALANCE`
FROM `ORG1`.`USER` U
LEFT JOIN (
SELECT
IFNULL(O.ID,0) as ID,
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,
COUNT(HD_TICKET.OWNER_ID) AS `BEGINING BALANCE`
FROM HD_TICKET
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
LEFT JOIN HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
WHERE HD_STATUS.STATE != 'closed'
GROUP BY O.ID
ORDER BY O.FULL_NAME) OPENTICKETS ON (OPENTICKETS.ID = U.ID)
LEFT JOIN (
SELECT
IFNULL(O.ID,0) as ID,
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,
COUNT(HD_TICKET.OWNER_ID) AS `NEW TICKETS`
FROM HD_TICKET
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
LEFT JOIN HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
WHERE HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY O.ID
ORDER BY O.FULL_NAME) NEWTICKETS ON (NEWTICKETS.ID = U.ID)

LEFT JOIN (
SELECT
IFNULL(O.ID,0) as ID,
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,
COUNT(HD_TICKET.OWNER_ID) AS `CLOSED TICKETS`
FROM HD_TICKET
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
LEFT JOIN HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
WHERE HD_STATUS.STATE = 'closed'
AND HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY O.ID
ORDER BY O.FULL_NAME) CLOSEDTICKETS ON (CLOSEDTICKETS.ID = U.ID)

WHERE U.USER_NAME
NOT IN ('kbox1248163264128256','TestUser', 'admin') AND (U.ROLE_ID = '1' OR U.ROLE_ID = '5')
group by U.ID
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

There are no answers at this time
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

Answer this question or Comment on this question for clarity