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 <spdegabrie...@gmail.com> > 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 fossil-users@lists.fossil-scm.org http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users