Hi,
Another tickets report – a summary report of ticket counts.
I’d love to be able to link the rows, columns, or counts to another report, but
I don’t think this is possible.
Stephen
--
SELECT subsystem,
-- Counts by status
count(CASE WHEN status="Open" THEN 1 else null end) as Open,
count(CASE WHEN status="Verified" THEN 1 else null end) as Verified,
count(CASE WHEN status="Review" THEN 1 else null end) as Review,
count(CASE WHEN status="Deferred" THEN 1 else null end) as Deferred,
count(CASE WHEN status="Fixed" THEN 1 else null end) as Fixed,
count(CASE WHEN status="Tested" THEN 1 else null end) as Tested,
--Counts by priority
count(CASE WHEN priority="Immediate" THEN 1 else null end) as Immediate,
count(CASE WHEN priority="High" THEN 1 else null end) as High,
count(CASE WHEN priority="Medium" THEN 1 else null end) as Medium,
count(CASE WHEN priority="Low" THEN 1 else null end) as Review,
count(CASE WHEN priority="Zero" THEN 1 else null end) as Zero,
-- make the row red if two or more tickets are open
CASE WHEN count(CASE WHEN status="Open" THEN 1 else null end)>=2 THEN
'#f2dcdc'
WHEN count(CASE WHEN status="Open" THEN 1 else null end)<2 THEN
'#ffffff'
ELSE '#c8c8c8' END as 'bgcolor'
FROM ticket
WHERE status<>"Closed"
GROUP BY subsystem
--
Sent from my iPhone
> On 30 Jun 2016, at 11:10, Stephen De Gabrielle <[email protected]>
> wrote:
>
> Hi,
>
> The default tickets with comments report doesn't seem to work.
> (The default examples don't seem to match the default schema)
>
> I'd appreciate it if list members could let me know if this works for them:
> --
> select
> CASE WHEN status IN ('new','active') THEN '#f2dcdc'
> WHEN status='review' THEN '#e8e8bd'
> WHEN status='fixed' THEN '#cfe8bd'
> WHEN status='tested' THEN '#bde5d6'
> WHEN status='defer' THEN '#cacae5'
> ELSE '#c8c8c8' END as 'bgcolor',
> substr(t.tkt_uuid,1,10) AS '#',
> datetime(t.tkt_mtime) AS 'mtime',
> t.type,
> t.subsystem,
> group_concat((tc.icomment || '<br> - '|| tc.login || ' - ' ||
> datetime(tc.tkt_mtime)),'<br><br>') as '_comments'
>
> FROM ticket t, ticketchng tc
> WHERE t.status<>"Closed" AND t.tkt_id IN (SELECT tc.tkt_id FROM ticketchng )
> GROUP BY t.tkt_id
> ORDER BY datetime(t.tkt_mtime) DESC
>
> ---
>
> Sent from my iPhone
_______________________________________________
fossil-users mailing list
[email protected]
http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users
