ITNinja

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