/build/static/layout/Breadcrumb_cap_w.png

SQL Reporting

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

Answers (1)

Posted by: igalloway 6 years ago
White Belt
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')
 
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