Mailing List Archive

Report to count tickets by month
As the subject indicates, I want to quickly show the ticket count by month.
I don't know if this is even possible,

Any help is greatly appreciated.

The below query only shows the header row containing the months but no
values.

SELECT
SUM(CASE strftime('%m', c3.value) WHEN '01' THEN 1 ELSE 0 END) AS 'Jan',
SUM(CASE strftime('%m', c3.value) WHEN '02' THEN 1 ELSE 0 END) AS 'Feb',
SUM(CASE strftime('%m', c3.value) WHEN '03' THEN 1 ELSE 0 END) AS 'Mar',
SUM(CASE strftime('%m', c3.value) WHEN '04' THEN 1 ELSE 0 END) AS 'Apr',
SUM(CASE strftime('%m', c3.value) WHEN '05' THEN 1 ELSE 0 END) AS 'May',
SUM(CASE strftime('%m', c3.value) WHEN '06' THEN 1 ELSE 0 END) AS 'Jun',
SUM(CASE strftime('%m', c3.value) WHEN '07' THEN 1 ELSE 0 END) AS 'Jul',
SUM(CASE strftime('%m', c3.value) WHEN '08' THEN 1 ELSE 0 END) AS 'Aug',
SUM(CASE strftime('%m', c3.value) WHEN '09' THEN 1 ELSE 0 END) AS 'Sep',
SUM(CASE strftime('%m', c3.value) WHEN '10' THEN 1 ELSE 0 END) AS 'Oct',
SUM(CASE strftime('%m', c3.value) WHEN '11' THEN 1 ELSE 0 END) AS 'Nov',
SUM(CASE strftime('%m', c3.value) WHEN '12' THEN 1 ELSE 0 END) AS 'Dec',
SUM(CASE strftime('%Y', c3.value) WHEN '2020' THEN 1 ELSE 0 END) AS
'TOTAL'

FROM
ticket t
LEFT OUTER JOIN ticket_custom c3 ON
(t.id = c3.ticket AND c3.name = 'expect_complete')
WHERE
t.status = 'closed' AND
((strftime('%s', c3.value) - t.time/1000000) >= '01/01/2020' AND
(strftime('%s', c3.value) - t.time/1000000) <= '12/31/2020')


--
You received this message because you are subscribed to the Google Groups "Trac Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to trac-users+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/trac-users/f0397d6c-0f1f-4cfe-9dbc-e3c1281d3de7o%40googlegroups.com.