/build/static/layout/Breadcrumb_cap_w.png
02/22/2018 631 views
I am not an SQL guy but I have been tasked with this project. I have attached the code in below
I want to add a row that gives me a yearly count.
So I would want it to give me ticket information from January-December 2017.
Any help on this would be great

Select 'Closed this month' as Title, Count(HD_TICKET.ID) as Amount
-- Count As 'test'
From
  USER Inner Join
  HD_TICKET
    On HD_TICKET.OWNER_ID = USER.ID Join
  HD_STATUS
    On HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
Where
  (HD_TICKET.HD_QUEUE_ID = 1) And
  ((HD_STATUS.STATE = 'Closed') And
  datediff(now(), HD_TICKET.CREATED) <= 30)

Union

SELECT  'Total Open Tickets' as Title, Count(HD_TICKET.ID) as Amount
-- count as 'test'  
FROM 
  HD_TICKET JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) 
WHERE 
  (HD_TICKET.HD_QUEUE_ID = 1) AND 
  (HD_STATUS.NAME != 'Closed')

Union

SELECT  'Open Over 30' as Title, Count(HD_TICKET.ID) as Amount
--  count As ' Number of Tickets'
FROM 
  HD_TICKET JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) 
WHERE 
  (HD_TICKET.HD_QUEUE_ID = 1) AND 
  ((HD_STATUS.NAME != 'Closed') AND 
  ((TIMESTAMP(HD_TICKET.CREATED) > NOW() OR 
  TIMESTAMP(HD_TICKET.CREATED) <= DATE_SUB(NOW(),INTERVAL 30 DAY)))) 

 Union

SELECT  'Open Over 60' as Title, Count(HD_TICKET.ID) as Amount
--  count As ' Number of Tickets'
FROM 
  HD_TICKET JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) 
WHERE 
  (HD_TICKET.HD_QUEUE_ID = 1) AND 
  ((HD_STATUS.NAME != 'Closed') AND 
  ((TIMESTAMP(HD_TICKET.CREATED) > NOW() OR 
  TIMESTAMP(HD_TICKET.CREATED) <= DATE_SUB(NOW(),INTERVAL 60 DAY))))  

Union

SELECT  'Open Over 90' as Title, Count(HD_TICKET.ID) as Amount
--  count As ' Number of Tickets'
FROM 
  HD_TICKET JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) 
WHERE 
  (HD_TICKET.HD_QUEUE_ID = 1) AND 
  ((HD_STATUS.NAME != 'Closed') AND 
  ((TIMESTAMP(HD_TICKET.CREATED) > NOW() OR 
  TIMESTAMP(HD_TICKET.CREATED) <= DATE_SUB(NOW(),INTERVAL 90 DAY))))  

Union

SELECT  'Open This Week' as Title, Count(HD_TICKET.ID) as Amount
-- COUNT  as 'Tickets created last 7 days'
FROM HD_TICKET  JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID 
 WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((Q.NAME like '%ITS%')) and datediff(now(), HD_TICKET.CREATED) <= 7

0 Comments   [ + ] Show comments

Comments


All Answers

3
Adding this to the end will give you the total amount of tickets in Queue ID 1 from Jan 1 2017 to Dec 31 2017

UNION
SELECT 'Opened This Year' AS Title,                       
        Count(HD_TICKET.ID) AS Amount -- COUNT  as 'Tickets created this year'
FROM HD_TICKET
JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID WHERE (HD_TICKET.HD_QUEUE_ID = 1)
AND (HD_TICKET.CREATED between '2017-01-01 00:00:00' and '2017-12-31 23:59:59')
Answered 02/22/2018 by: igalloway
White Belt