/build/static/layout/Breadcrumb_cap_w.png

Need help breaking this down to weeks

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

1 Comment   [ + ] Show comment
  • 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??? - annleacock 9 years ago

Answers (1)

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
Posted by: dchristian 12 years ago
Red Belt
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

Comments:
  • You are a genius! I thank you and the hair I haven't pulled out thanks you. - DragonCrone 12 years ago

Don't be a Stranger!

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

Sign up! or login

View more:

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