I created this report for showing how many tickets get opened each month, but now they want it broken down to weekly. Just need a count, no detail. i.e. Week 1 = 325; Week 2 = 125. The reason is they want to track whether we are getting more or less calls.

Here's what I came up with for the monthly, but I don't have a clue how to break it down by weeks. BTW, they want it back to when we first started using Kbox.

SELECT OPEN.MONTH,
OPEN.YEAR,
OPEN.OPEN,
CLOSED.CLOSED
FROM (SELECT CASE
WHEN MONTH(T.CREATED) = 1 THEN 'JAN'
WHEN MONTH(T.CREATED) = 2 THEN 'FEB'
WHEN MONTH(T.CREATED) = 3 THEN 'MAR'
WHEN MONTH(T.CREATED) = 4 THEN 'APR'
WHEN MONTH(T.CREATED) = 5 THEN 'MAY'
WHEN MONTH(T.CREATED) = 6 THEN 'JUN'
WHEN MONTH(T.CREATED) = 7 THEN 'JUL'
WHEN MONTH(T.CREATED) = 8 THEN 'AUG'
WHEN MONTH(T.CREATED) = 9 THEN 'SEP'
WHEN MONTH(T.CREATED) = 10 THEN 'OCT'
WHEN MONTH(T.CREATED) = 11 THEN 'NOV'
WHEN MONTH(T.CREATED) = 12 THEN 'DEC'
END AS 'MONTH',
YEAR (T.CREATED) AS YEAR,
COUNT(*) AS OPEN
FROM HD_TICKET T
GROUP BY MONTH,
YEAR
ORDER BY YEAR,
MONTH) OPEN,
(SELECT CASE
WHEN MONTH(T.TIME_CLOSED) = 1 THEN 'JAN'
WHEN MONTH(T.TIME_CLOSED) = 2 THEN 'FEB'
WHEN MONTH(T.TIME_CLOSED) = 3 THEN 'MAR'
WHEN MONTH(T.TIME_CLOSED) = 4 THEN 'APR'
WHEN MONTH(T.TIME_CLOSED) = 5 THEN 'MAY'
WHEN MONTH(T.TIME_CLOSED) = 6 THEN 'JUN'
WHEN MONTH(T.TIME_CLOSED) = 7 THEN 'JUL'
WHEN MONTH(T.TIME_CLOSED) = 8 THEN 'AUG'
WHEN MONTH(T.TIME_CLOSED) = 9 THEN 'SEP'
WHEN MONTH(T.TIME_CLOSED) = 10 THEN 'OCT'
WHEN MONTH(T.TIME_CLOSED) = 11 THEN 'NOV'
WHEN MONTH(T.TIME_CLOSED) = 12 THEN 'DEC'
END AS 'MONTH',
YEAR (T.TIME_CLOSED) AS YEAR,
COUNT(*) AS CLOSED
FROM HD_TICKET T
WHERE T.TIME_CLOSED NOT LIKE '0000%'
GROUP BY MONTH,
YEAR
ORDER BY YEAR,
MONTH) CLOSED
WHERE OPEN.MONTH = CLOSED.MONTH
AND OPEN.YEAR = CLOSED.YEAR
Break on Columns = YEAR

We don't even need the closed ticket count.

Thanks in advance,
D
Answer Summary:
SELECT CASE WHEN MONTH(T.CREATED) = 1 THEN 'JAN' WHEN MONTH(T.CREATED) = 2 THEN 'FEB' WHEN MONTH(T.CREATED) = 3 THEN 'MAR' WHEN MONTH(T.CREATED) = 4 THEN 'APR' WHEN MONTH(T.CREATED) = 5 THEN 'MAY' WHEN MONTH(T.CREATED) = 6 THEN 'JUN' WHEN MONTH(T.CREATED) = 7 THEN 'JUL' WHEN MONTH(T.CREATED) = 8 THEN 'AUG' WHEN MONTH(T.CREATED) = 9 THEN 'SEP' WHEN MONTH(T.CREATED) = 10 THEN 'OCT' WHEN MONTH(T.CREATED) = 11 THEN 'NOV' WHEN MONTH(T.CREATED) = 12 THEN 'DEC' END AS 'MONTH', CASE WHEN DAYOFMONTH(T.CREATED) < 8 THEN 'WEEK1' WHEN DAYOFMONTH(T.CREATED) < 15 THEN 'WEEK2' WHEN DAYOFMONTH(T.CREATED) < 22 THEN 'WEEK3' ELSE 'WEEK4' END AS WEEK, YEAR (T.CREATED) AS YEAR, COUNT(*) AS OPEN FROM HD_TICKET T GROUP BY MONTH, WEEK, YEAR ORDER BY YEAR, MONTH(T.CREATED), WEEK
Cancel
1 Comment   [ + ] Show Comment

Comments

  • This is a great report, I'm wondering if there was a way you could help to modify this to only look for a specific category, for instance Access::Lan Locked???
Please log in to comment

Answers

1
I think this will do what your looking for:

SELECT CASE
WHEN MONTH(T.CREATED) = 1 THEN 'JAN'
WHEN MONTH(T.CREATED) = 2 THEN 'FEB'
WHEN MONTH(T.CREATED) = 3 THEN 'MAR'
WHEN MONTH(T.CREATED) = 4 THEN 'APR'
WHEN MONTH(T.CREATED) = 5 THEN 'MAY'
WHEN MONTH(T.CREATED) = 6 THEN 'JUN'
WHEN MONTH(T.CREATED) = 7 THEN 'JUL'
WHEN MONTH(T.CREATED) = 8 THEN 'AUG'
WHEN MONTH(T.CREATED) = 9 THEN 'SEP'
WHEN MONTH(T.CREATED) = 10 THEN 'OCT'
WHEN MONTH(T.CREATED) = 11 THEN 'NOV'
WHEN MONTH(T.CREATED) = 12 THEN 'DEC'
END AS 'MONTH',
CASE
WHEN DAYOFMONTH(T.CREATED) < 8 THEN 'WEEK1'
WHEN DAYOFMONTH(T.CREATED) < 15 THEN 'WEEK2'
WHEN DAYOFMONTH(T.CREATED) < 22 THEN 'WEEK3'
ELSE 'WEEK4'
END AS WEEK,
YEAR (T.CREATED) AS YEAR,
COUNT(*) AS OPEN
FROM HD_TICKET T
GROUP BY MONTH,
WEEK,
YEAR
ORDER BY YEAR,
MONTH(T.CREATED),
WEEK
Answered 03/23/2012 by: dchristian
Red Belt

  • You are a genius! I thank you and the hair I haven't pulled out thanks you.
Please log in to comment
Answer this question or Comment on this question for clarity