/build/static/layout/Breadcrumb_cap_w.png

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.


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 1 year ago
Red Belt
0

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 1 year ago
  • The only thing I would like to change is for current day. CurDate???? - lockej 1 year 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 1 year 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 1 year 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 1 year ago
  • Chuck, thank you so much. Worked perfectly. I really appreciate it. - lockej 1 year ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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