K1000 Report Open and Closed ticket for the day- on more then one queue.
Just looking for total numbers of tickets closed and opened daily.
I can do each one separately , but need report together.
Here is Opened:
SELECT
HD_QUEUE.NAME AS 'Queue',
COUNT(T.id) AS 'Count'
FROM
HD_QUEUE
INNER JOIN HD_TICKET T ON HD_QUEUE.ID = T.HD_QUEUE_ID
JOIN HD_STATUS ON (HD_STATUS.ID = T.HD_STATUS_ID)
WHERE
(
DATEDIFF(NOW(), T.CREATED) < 1
AND T.HD_QUEUE_ID IN (5,13)
)
GROUP BY Queue
Here is Closed;
SELECT
HD_QUEUE.NAME AS 'Queue',
COUNT(T.id) AS 'Count'
FROM
HD_QUEUE
INNER JOIN HD_TICKET T ON HD_QUEUE.ID = T.HD_QUEUE_ID
JOIN HD_STATUS ON (HD_STATUS.ID = T.HD_STATUS_ID)
WHERE
(
DATEDIFF(NOW(), T.TIME_CLOSED) < 1
AND T.HD_QUEUE_ID IN (5,13)
)
GROUP BY Queue
Trying desperately to join these two queries to make one report.
Thanks in advanced.
Answers (1)
Top Answer
Here you go:
SELECT Q.NAME,
COUNT(HD_TICKET.ID) as 'Opened',
SUM(CASE
WHEN HD_TICKET.TIME_CLOSED != '0000-00-00 00:00:00' THEN 1
ELSE 0
END) As 'Closed'
FROM
ORG1.HD_TICKET
LEFT JOIN HD_QUEUE as Q on Q.ID = HD_TICKET.HD_QUEUE_ID
WHERE DATE(HD_TICKET.CREATED) = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))
AND Q.ID in (5,13)
GROUP BY Q.ID
Comments:
-
thank you so much. Let me try it. - lockej 4 years ago
-
The only thing I would like to change is for current day. CurDate???? - lockej 4 years ago
-
This:
DATE(HD_TICKET.CREATED) = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))
becomes this:
DATE(HD_TICKET.CREATED) = DATE(NOW())
or
DATE(HD_TICKET.CREATED) = TODAY() - chucksteel 4 years ago-
I am getting this now:
mysqli error: [1370: execute command denied to user 'R1'@'%' for routine 'ORG1.TODAY'] in EXECUTE(\n"SELECT Q.NAME,\nCOUNT(HD_TICKET.ID) as 'Opened',\nSUM(CASE \n WHEN HD_TICKET.TIME_CLOSED != '0000-00-00 00:00:00' THEN 1\n ELSE 0\n END) As 'Closed'\nFROM \nORG1.HD_TICKET\nLEFT JOIN HD_QUEUE as Q on Q.ID = HD_TICKET.HD_QUEUE_ID\nWHERE DATE(HD_TICKET.CREATED) = TODAY()\nAND Q.ID in (5,13)\nGROUP BY Q.ID LIMIT 0")\n
SELECT Q.NAME,
COUNT(HD_TICKET.ID) as 'Opened',
SUM(CASE
WHEN HD_TICKET.TIME_CLOSED != '0000-00-00 00:00:00' THEN 1
ELSE 0
END) As 'Closed'
FROM
ORG1.HD_TICKET
LEFT JOIN HD_QUEUE as Q on Q.ID = HD_TICKET.HD_QUEUE_ID
WHERE DATE(HD_TICKET.CREATED) = TODAY()
AND Q.ID in (5,13)
GROUP BY Q.ID - lockej 4 years ago -
I wasn't sure if TODAY() was a valid MariaDB function, should have tested that.
Use this one:
DATE(HD_TICKET.CREATED) = DATE(NOW()) - chucksteel 4 years ago
-
Chuck, thank you so much. Worked perfectly. I really appreciate it. - lockej 4 years ago
