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