ITNinja

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