/build/static/layout/Breadcrumb_cap_w.png

Number of Tickets Open and Closed by Quarter

Here is a report that will show the number of tickets open and closed by quarter.

Looks great if you have it set to brake on year.


SELECT OPEN.QUARTER,
  OPEN.YEAR,
  OPEN.OPEN,
  CLOSED.CLOSED   
  FROM (SELECT CASE
WHEN MONTH(T.CREATED) < 4 THEN  'Q1'
WHEN MONTH(T.CREATED) < 7 THEN  'Q2'
WHEN MONTH(T.CREATED) < 10 THEN  'Q3'
ELSE 'Q4'
  END AS 'QUARTER',
  YEAR (T.CREATED) AS YEAR,
              COUNT(*) AS OPEN
FROM HD_TICKET T
GROUP BY QUARTER,
YEAR
ORDER BY YEAR,
QUARTER) OPEN,
(SELECT CASE
          WHEN MONTH(T.TIME_CLOSED) < 4 THEN  'Q1'
          WHEN MONTH(T.TIME_CLOSED) < 7 THEN  'Q2'
          WHEN MONTH(T.TIME_CLOSED) < 10 THEN  'Q3'
          ELSE 'Q4'
  END AS 'QUARTER',
  YEAR (T.TIME_CLOSED) AS YEAR,
  COUNT(*) AS CLOSED
FROM HD_TICKET T
WHERE T.TIME_CLOSED NOT LIKE '0000%'
GROUP BY QUARTER,
YEAR
ORDER BY YEAR,
QUARTER) CLOSED
WHERE OPEN.QUARTER = CLOSED.QUARTER
  AND OPEN.YEAR = CLOSED.YEAR 


Comments

  • Do you have any ideas on how to make this "number of tickets opened and closed per day"? I'd love to have a running list so I can track activity over the long term. - michaellynton 10 years ago
This post is locked

Don't be a Stranger!

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

Sign up! or login
Post

Related Posts

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