OMG Steve ! Thank you. Surely I know the SQL language but I won't be able to pull this one.
Is there a requirement of a minimal mysql version for it to work ? Thanks again, Ionel On 02/10/2011 03:50 PM, Steve Durbin wrote:
Ionel,
This huge bit of SQL (for MySQL) will give you what you want. There's no
'stats' way of doing it I'm aware of. Note that a ticket that was closed then
reopened will be counted twice. The result here was:
+--------------------+---------+
| Fix Band | Tickets |
+--------------------+---------+
| a. 0-4 Hours | 2590 |
| b. 4 Hours - 1 Day | 502 |
| c. 1 - 8 Days | 657 |
| d.>8 days | 180 |
+--------------------+---------+
4 rows in set (1.82 sec)
SELECT
CASE
WHEN (SUBDATE(th.change_time, INTERVAL 4 HOUR)<= t.create_time) THEN 'a.
0-4 Hours'
WHEN ((SUBDATE(th.change_time, INTERVAL 1 DAY)<= t.create_time)
AND (SUBDATE(th.change_time, INTERVAL 4 HOUR)> t.create_time)) THEN
'b. 4 Hours - 1 Day'
WHEN ((SUBDATE(th.change_time, INTERVAL 8 DAY)<= t.create_time)
AND (SUBDATE(th.change_time, INTERVAL 1 DAY)> t.create_time)) THEN
'c. 1 - 8 Days'
ELSE 'd.>8 days' END as 'Fix Band',
COUNT(*) as 'Tickets'
FROM ticket AS t
INNER JOIN ticket_state s ON t.ticket_state_id = s.id
INNER JOIN ticket_state_type st ON s.type_id = st.id and st.name = 'closed'
INNER JOIN ticket_history th on t.id = th.ticket_id and th.state_id =
t.ticket_state_id
INNER JOIN ticket_history_type tht on th.history_type_id = tht.id
and tht.name in ('NewTicket','StateUpdate')
WHERE t.create_time> SUBDATE(NOW(),INTERVAL 1 MONTH)
AND th.change_time is not null
GROUP BY CASE
WHEN (SUBDATE(th.change_time, INTERVAL 4 HOUR)<= t.create_time) THEN 'a.
0-4 Hours'
WHEN ((SUBDATE(th.change_time, INTERVAL 1 DAY)<= t.create_time)
AND (SUBDATE(th.change_time, INTERVAL 4 HOUR)> t.create_time)) THEN
'b. 4 Hours - 1 Day'
WHEN ((SUBDATE(th.change_time, INTERVAL 8 DAY)<= t.create_time)
AND (SUBDATE(th.change_time, INTERVAL 1 DAY)> t.create_time)) THEN
'c. 1 - 8 Days'
ELSE 'd.>8 days' END
ORDER BY CASE
WHEN (SUBDATE(th.change_time, INTERVAL 4 HOUR)<= t.create_time) THEN 'a.
0-4 Hours'
WHEN ((SUBDATE(th.change_time, INTERVAL 1 DAY)<= t.create_time)
AND (SUBDATE(th.change_time, INTERVAL 4 HOUR)> t.create_time)) THEN
'b. 4 Hours - 1 Day'
WHEN ((SUBDATE(th.change_time, INTERVAL 8 DAY)<= t.create_time)
AND (SUBDATE(th.change_time, INTERVAL 1 DAY)> t.create_time)) THEN
'c. 1 - 8 Days'
ELSE 'd.>8 days' END;
--
Steve Durbin
Group Manager: Applications Delivery/Cyfarwyddwr Grŵp: Trosglwyddo Rhaglenni
Bridgend County Borough Council/Cyngor Bwrdeistref Sirol Pen-y-Bont ar Ogwr
Tel/Rhif ffon: 01656 642113
Mob/ffon symudol: 07976 271559
Fax/Ffacs: 01656 642125
Web/We: http://www.bridgend.gov.uk
E-mail may be automatically logged, monitored and/or recorded for legal
purposes.
Please do not print this email unless absolutely necessary.
E-bost yn cael ei logio, ei monitro a/neu ei chofnodi yn awtomatig am resymau
cyfreithiol
Peidiwch ag argraffu’r neges e-bost hon oni bai fod hynny’n gwbl angenrheidiol.
________________________________________________________________________
This e-mail and any attachments transmitted with it represents the
views of the individual(s) who sent them and should not be regarded
as the official view of Bridgend County Borough Council. The contents
are confidential and intended solely for the use of the addressee. If
you have received it in error, please inform the system administrator
on (+44) 01656 642111.
This e-mail and any attachments have been scanned with 'MessageLabs SkyScan' -
http://www.messagelabs.com/
________________________________________________________________________
Mae'r e-bost hwn ac unrhyw atodiadau a drosglwyddir gydag ef yn cynrychioli
safbwyntiau'r unigolyn a'i anfonodd (unigolion a'u hanfonodd) ac ni ddylid eu
hystyried fel safbwynt swyddogol Cyngor Bwrdeistref Sirol Pen-y-bont ar Ogwr.
Mae'r cynnwys yn gyfrinachol ac wedi'i fwriadu ar gyfer y sawl y'i cyfeiriwyd
ato yn unig. Os ydych wedi ei dderbyn mewn camgymeriad, rhowch wybod i
weinyddwr y system ar (+44) 01656 642111.
Mae'r e-bost hwn ac unrhyw atodiadau wedi cael eu sganio gyda 'MessageLabs
SkyScan' - http://www.messagelabs.com/
________________________________________________________________________
---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
-- Ionel GARDAIS Tech'Advantage CIO - IT Team manager
<<attachment: ionel_gardais.vcf>>
--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
