/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


K1000 Report Open and Closed ticket for the day- on more then one queue.

03/18/2020 216 views

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.

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0

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


Answered 03/20/2020 by: chucksteel
Red Belt

  • thank you so much. Let me try it.
  • The only thing I would like to change is for current day. CurDate????
    • 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()
      • 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
      • I wasn't sure if TODAY() was a valid MariaDB function, should have tested that.
        Use this one:
        DATE(HD_TICKET.CREATED) = DATE(NOW())
  • Chuck, thank you so much. Worked perfectly. I really appreciate it.
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ