Hi Chen,
Below are two which I tend to use for all my own projects. I'm not
sure if they are "the best SQL ever" but work fine for my purposes.
"All but closed tickets" gives you a view sorted by status, priority,
severity and submission time. "Counters" may be used for auditing
purposes to show the progress and status of the project. Hope it
helps.
Cheers,
Jacek
*** All but closed ***
SELECT
CASE WHEN status IN ('Open','Verified') THEN '#f2dcdc'
WHEN status='Blocked' THEN '#ffcccc'
WHEN status='Review' THEN '#e8e8e8'
WHEN status='Fixed' THEN '#cfe8bd'
WHEN status='Tested' THEN '#bde5d6'
WHEN status='Deferred' THEN '#cacae5'
ELSE '#c8c8c8' END AS 'bgcolor',
substr(tkt_uuid,1,10) AS '#',
priority,
severity,
type,
status,
subsystem,
CASE WHEN length(title) > 60 THEN substr(title,0,57) || '...'
ELSE title END AS 'title'
FROM (
SELECT *,
CASE WHEN status IN ('Open','Verified','Blocked') THEN 0
WHEN status='Review' THEN 1
WHEN status='Fixed' THEN 2
WHEN status='Tested' THEN 3
WHEN status='Deferred' THEN 4
ELSE 5 END AS 'st',
CASE WHEN priority='Immediate' THEN 0
WHEN priority='High' THEN 1
WHEN priority='Medium' THEN 2
WHEN priority='Low' THEN 3
ELSE 4 END AS 'p',
CASE WHEN severity='Critical' THEN 0
WHEN severity='Severe' THEN 1
WHEN severity='Important' THEN 2
WHEN severity='Minor' THEN 3
ELSE 4 END AS 's'
FROM ticket
WHERE status != 'Closed'
ORDER BY st, p, s, tkt_mtime)
*** Counters ***
SELECT
CASE WHEN status IN ('Open','Verified') THEN '#f2dcdc'
WHEN status='Blocked' THEN '#ffcccc'
WHEN status='Review' THEN '#e8e8e8'
WHEN status='Fixed' THEN '#cfe8bd'
WHEN status='Tested' THEN '#bde5d6'
WHEN status='Deferred' THEN '#cacae5'
ELSE '#b8b8b8' END AS 'bgcolor',
status AS 'Status',
count (*) AS 'Number of tickets'
FROM ticket GROUP BY status
UNION
SELECT '#ffffff', 'Total', count(*)
FROM ticket
ORDER BY status
2012/5/31 Chen, Zon <[email protected]>:
>
> Hi, I'm setting some reports for our Fossil ticket system, but I'm not
> immediately familiar with SQL. I've been muddling my way through with the
> SQLLite documentation, but it would be really nice to see/steal some common
> examples, such as the reports available on the fossil-scm.org.
>
> Would it be possible to please make the SQL of the reports used by the fossil
> project viewable by anonymous users? Or alternatively, if they could be
> cut-pasted into the wiki.
>
> Thanks,
> zchen
>
> _______________________________________________
> fossil-users mailing list
> [email protected]
> http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users
_______________________________________________
fossil-users mailing list
[email protected]
http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users
