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)
Please log in to answer
Posted by:
igalloway
6 years ago
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')
