|
Report |
cols |
sqlcode |
|
Recently changed and active tickets |
white Color codes:
#f2dcdc Active Bugs
#e8e8bd Active Issues
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
SELECT
CASE WHEN status IN ('new','active') THEN
CASE type WHEN 'code' THEN '#f2dcdc' ELSE '#e8e8bd' END
WHEN status='fixed' THEN '#cfe8bd'
WHEN status='tested' THEN '#bde5d6'
WHEN status IN ('defer','review') THEN 'cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE (changetime>now()-604800 OR status IN ('new','active'))
ORDER BY tn DESC
|
|
All tickets of mine by priority |
white Priority color codes:
#f2dcdc 1
#e8e8bd 2
#cfe8bd 3
#cacae5 4
#c8c8c8 5
|
SELECT
CASE priority WHEN 1 THEN '#f2dcdc'
WHEN 2 THEN '#e8e8bd'
WHEN 3 THEN '#cfe8bd'
WHEN 4 THEN '#cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE assignedto = user()
ORDER BY priority, tn
|
|
Active tickets of mine by priority |
white Priority color codes:
#f2dcdc 1
#e8e8bd 2
#cfe8bd 3
#cacae5 4
#c8c8c8 5
|
SELECT
CASE priority WHEN 1 THEN '#f2dcdc'
WHEN 2 THEN '#e8e8bd'
WHEN 3 THEN '#cfe8bd'
WHEN 4 THEN '#cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE status IN ('new','active') AND assignedto = user()
ORDER BY priority, tn
|
|
GitTrac tickets |
#ffffff Key:
#f2dcdc Active
#e8e8e8 Review
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
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',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket WHERE subsystem='gittrac'
|
|
Check-ins by a single user |
|
SELECT
chng(cn) as 'Change #',
ldate(date) as 'Time',
message as 'Comment'
FROM chng
WHERE user=option('User','SELECT id FROM user')
ORDER BY date DESC
|
|
Report SQL Examples |
#ffffff Key:
#f2dcdc Active
#e8e8e8 Review
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
select
wiki( '{link:rptview?rn=' || rn || ' ' || title || '}' ) as Report,
cols ,
sqlcode
from reportfmt
|
|
All active tickets w/descriptions |
white Priority color codes:
#f2dcdc 1
#e8e8bd 2
#cfe8bd 3
#cacae5 4
#c8c8c8 5
|
SELECT
CASE priority WHEN 1 THEN '#f2dcdc'
WHEN 2 THEN '#e8e8bd'
WHEN 3 THEN '#cfe8bd'
WHEN 4 THEN '#cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title',
description AS '_Description',
remarks AS '_Remarks'
FROM ticket
WHERE status IN ('new','active')
|
|
show table content |
|
SELECT * from enums
|
|
Check-ins since... |
|
SELECT
cn as 'Change #',
user as "By",
ldate(date) as 'Time',
message as 'Comment'
FROM chng
WHERE cn >= aux("Checkin")
ORDER BY cn DESC
|
|
Ticket History |
|
SELECT
tn AS '#',
user AS 'User',
sdate(chngtime) AS 'Change Time',
fieldid AS 'Field ID',
oldval AS 'Old',
newval AS 'New'
FROM tktchng
WHERE tn=aux('Ticket','1')
|
|
Version of the SQLite Database Engine in use. |
|
SELECT sqlite_version()
|
|
Users |
|
SELECT * FROM user ORDER BY id
|
|
Copy Of Check-ins by a single user within the past 30 days |
|
SELECT
*
FROM chng
WHERE date>=now()-2592000
AND user=option('User','SELECT id FROM user')
ORDER BY date DESC
|
|
Teste do Carmo |
#ffffff Key:
#f2dcdc Active
#e8e8e8 Review
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
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',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
|
|
Milestones |
|
SELECT
cn AS 'Ms',
CASE milestone WHEN 1 THEN 'Release'
WHEN 2 THEN 'Event'
ELSE 'Unknown' END as 'Type',
user AS 'By',
directory AS 'In',
message AS 'Message'
FROM chng
WHERE milestone>0
ORDER by date DESC
|
|
Branches |
|
SELECT
branch
FROM chng
WHERE branch!=''
GROUP BY branch
|
|
Wiki Search |
|
SELECT
name AS 'Name',
sdate(-invtime) AS 'When',
who AS 'Who',
ipaddr AS 'IP'
FROM wiki
WHERE text LIKE '%' || aux('Search') || '%'
ORDER by invtime
|
|
Ticket counts by status |
|
SELECT
status,
count(case when type='code' then 'x' end),
count(case when type='doc' then 'x' end),
count(case when type='new' then 'x' end),
count(case when type NOT IN ('code','doc','new') then 'x' end),
count(*)
FROM ticket GROUP BY status
UNION
SELECT
'TOTAL' AS 'Status',
count(case when type='code' then 'x' end) as 'Code Bugs',
count(case when type='doc' then 'x' end) as 'Doc Bugs',
count(case when type='new' then 'x' end) as 'Enhancements',
count(case when type NOT IN ('code','doc','new') then 'x' end)
as 'Other',
count(*) AS 'All Types'
FROM ticket
ORDER BY [All Types]
|
|
Tickets I need to work on |
#ffffff Key:
#ffaa99 New
#f2dcdc Active
#e8e8e8 Review
#bde5d6 Tested
#cacae5 Deferred
|
SELECT
CASE WHEN t.status='new' THEN '#ffaa99'
WHEN t.status='active' THEN '#f2dcdc'
WHEN t.status='review' THEN '#e8e8bd'
WHEN t.status='tested' THEN '#bde5d6'
ELSE '#cacae5' END as 'bgcolor',
t.tn AS '#',
e.value AS 'Type',
t.status AS 'Status',
sdate(t.origtime) AS 'Created',
t.owner AS 'By',
t.subsystem AS 'Subsys',
sdate(t.changetime) AS 'Changed',
t.assignedto AS 'Assigned',
t.severity AS 'Svr',
t.priority AS 'Pri',
t.title AS 'Title'
FROM enums e, ticket t
WHERE e.type='type'
AND e.name=t.type
AND t.status NOT IN ('fixed','closed')
AND t.assignedto=user()
ORDER BY t.priority, t.severity, t.origtime
|
|
Copy Of Active tickets |
#ffffff Key:
#f2dcdc Active
#e8e8e8 Review
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c2c8c8 Closed
|
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',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE status='active' and type like aux('type','%')
ORDER BY tn DESC
|
|
Changed tickets couple days ago |
white Color codes:
#f2dcdc Active Bugs
#e8e8bd Active Issues
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
SELECT
CASE WHEN status IN ('new','active') THEN
CASE type WHEN 'code' THEN '#f2dcdc' ELSE '#e8e8bd' END
WHEN status='fixed' THEN '#cfe8bd'
WHEN status='tested' THEN '#bde5d6'
WHEN status IN ('defer','review') THEN 'cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE changetime>now()-aux('Days',7)*24*60*60*10
ORDER BY tn DESC
|
|
Open Tickets with Attachments |
|
SELECT
ticket.tn AS '#',
ticket.status AS 'State',
attachment.fname AS 'File',
ticket.title AS 'Title'
FROM ticket, attachment
WHERE ticket.status IN ('new','active') AND attachment.tn=ticket.tn
|
|
Database Tables |
|
select * from SQLITE_MASTER
where type = 'table'
order by name
|
|
Milestones |
|
SELECT
cn AS 'Cn',
milestone AS 'Type',
sdate(date) AS 'Created',
user AS 'By',
message AS 'Log'
FROM chng
WHERE milestone <> 0
|
|
Showstoppers |
#ffffff Key:
#f2dcdc Active
#e8e8e8 Review
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
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',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title',
description AS '_Description',
remarks AS '_Remarks'
FROM ticket
WHERE ( priority IN ('1','2') AND status IN ('new','review','defer','active') )
ORDER BY severity, tn
|
|
jan's Search for a ticket number |
#ffffff Key:
#f2dcdc Active
#e8e8e8 Review
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
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',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE tn=aux('tn', null)
|
|
Active tickets by change date |
white Priority color codes:
#f2dcdc 1
#e8e8bd 2
#cfe8bd 3
#cacae5 4
#c8c8c8 5
|
SELECT
CASE priority WHEN 1 THEN '#f2dcdc'
WHEN 2 THEN '#e8e8bd'
WHEN 3 THEN '#cfe8bd'
WHEN 4 THEN '#cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title',
description as 'Opis'
FROM ticket
WHERE status IN ('new','active')
ORDER BY changetime ASC, tn
|
|
Summary |
|
SELECT
assignedto AS 'Programmer',
count(case when status='new' OR status='active' then 'x' end) AS 'Open',
count(case when status='fixed' then 'x' end) AS 'Resolved',
count(case when status='closed' then 'x' end) AS 'Closed',
count(case when status NOT IN ('new','active','fixed','closed') then 'x' end) AS 'Other',
count(*) AS 'Total'
FROM ticket GROUP BY assignedto
ORDER BY [assignedto]
|
|
Active tickets grouped and ordered |
white Priority color codes:
#f2dcdc 1
#e8e8bd 2
#cfe8bd 3
#cacae5 4
#c8c8c8 5
|
SELECT
CASE priority WHEN 1 THEN '#f2dcdc'
WHEN 2 THEN '#e8e8bd'
WHEN 3 THEN '#cfe8bd'
WHEN 4 THEN '#cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE status IN ('new','active')
GROUP BY type
ORDER BY priority
|
|
all closed tickets |
#ffffff Key:
#f2dcdc Active
#e8e8e8 Review
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
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',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE status ='closed'
|
|
Priority ONE tickets |
white Priority color codes:
#f2dcdc 1
#e8e8bd 2
#cfe8bd 3
#cacae5 4
#c8c8c8 5
|
SELECT
CASE priority WHEN 1 THEN '#f2dcdc'
WHEN 2 THEN '#e8e8bd'
WHEN 3 THEN '#cfe8bd'
WHEN 4 THEN '#cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE ( priority IN ('1') AND status IN ('new','active') )
ORDER BY severity, tn
|
|
Recently closed tickets |
white Color codes:
#f2dcdc Active Bugs
#e8e8bd Active Issues
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
SELECT
CASE WHEN status IN ('new','active') THEN
CASE type WHEN 'code' THEN '#f2dcdc' ELSE '#e8e8bd' END
WHEN status='fixed' THEN '#cfe8bd'
WHEN status='tested' THEN '#bde5d6'
WHEN status IN ('defer','review') THEN 'cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE (changetime>now()-604800 AND status IN ('fixed','tested'))
ORDER BY tn DESC
|
|
Check-ins by a any user within the past 60 days |
#ffffff Key:
#f2dcdc Active
#e8e8bd Review
#cfe8bd Comment
|
SELECT
cn as 'Change #',
user as "By",
ldate(date) as 'Time',
message as 'Comment'
FROM chng
WHERE date<now()
ORDER BY date DESC
|
|
All Tickets by Status |
#ff00ff Key:
#eeeeee New
#f2dcdc Active
#e8e8bd Review
#cfe8bd Fixed
#bdefd6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
SELECT
CASE WHEN status='new' THEN '#eeeeee'
WHEN status='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',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
ORDER BY status
|
|
All unclosed tickets |
white Status color codes:
#f2dcdc Active
#e8e8bd Review
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
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',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE status!='closed'
|
|
All Tickets including closed |
#ffffff Key:
#f2dcdc Active
#e8e8e8 Review
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
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'
WHEN status='closed' THEN '#c8c8c8'
ELSE '#c8c8c8' END AS 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
|
|
Unimplemented Enhancement Requests |
white Priority color codes:
#f2dcdc 1
#e8e8bd 2
#cfe8bd 3
#cacae5 4
#c8c8c8 5
|
SELECT
CASE priority WHEN 1 THEN '#f2dcdc'
WHEN 2 THEN '#e8e8bd'
WHEN 3 THEN '#cfe8bd'
WHEN 4 THEN '#cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE status IN ('new','active') AND type='new'
ORDER BY tn
|
|
Unfixed Code Defects |
white Priority color codes:
#f2dcdc 1
#e8e8bd 2
#cfe8bd 3
#cacae5 4
#c8c8c8 5
|
SELECT
CASE priority WHEN 1 THEN '#f2dcdc'
WHEN 2 THEN '#e8e8bd'
WHEN 3 THEN '#cfe8bd'
WHEN 4 THEN '#cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE status IN ('new','active') AND type='code'
ORDER BY tn
|
|
Active tickets |
#ffffff Key:
#f2dcdc Active
#e8e8e8 Review
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
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',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE status='active'
ORDER BY tn DESC
|
|
All active tickets |
white Priority color codes:
#f2dcdc 1
#e8e8bd 2
#cfe8bd 3
#cacae5 4
#c8c8c8 5
|
SELECT
CASE priority WHEN 1 THEN '#f2dcdc'
WHEN 2 THEN '#e8e8bd'
WHEN 3 THEN '#cfe8bd'
WHEN 4 THEN '#cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE status IN ('new','active')
|
|
Date of last change for all wiki pages |
|
SELECT
name as 'Name',
ldate(max(-invtime)) as 'Date Modified'
FROM
wiki
GROUP BY name
ORDER BY max(-invtime)
|
|
Derived Tickets |
#ffffff Key:
#f2dcdc Active
#e8e8e8 Review
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
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',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
where derivedfrom is not null
|
|
Active tickets by priority |
white Priority color codes:
#f2dcdc 1
#e8e8bd 2
#cfe8bd 3
#cacae5 4
#c8c8c8 5
|
SELECT
CASE priority WHEN 1 THEN '#f2dcdc'
WHEN 2 THEN '#e8e8bd'
WHEN 3 THEN '#cfe8bd'
WHEN 4 THEN '#cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE status IN ('new','active')
ORDER BY priority, tn
|
|
Check-ins by a single user within the past 30 days |
|
SELECT
cn as 'Change #',
ldate(date) as 'Time',
message as 'Comment'
FROM chng
WHERE date>=now()-2592000
AND user=option('User','SELECT id FROM user')
ORDER BY date DESC
|
|
Tickets associated with a particular user |
#ffffff Key:
#f2dcdc Active
#e8e8e8 Review
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
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',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE owner=aux('User',user()) OR assignedto=aux('User',user())
|
|
Recently changed and active tickets sorted by priority |
white Color codes:
#f2dcdc Active Bugs
#e8e8bd Active Issues
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
SELECT
CASE WHEN status IN ('new','active') THEN
CASE type WHEN 'code' THEN '#f2dcdc' ELSE '#e8e8bd' END
WHEN status='fixed' THEN '#cfe8bd'
WHEN status='tested' THEN '#bde5d6'
WHEN status IN ('defer','review') THEN 'cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title'
FROM ticket
WHERE (changetime>now()-604800 OR status IN ('new','active'))
ORDER BY priority, tn DESC
|
|
Recently changed and active tickets w/descriptions |
white Color codes:
#f2dcdc Active Bugs
#e8e8bd Active Issues
#cfe8bd Fixed
#bde5d6 Tested
#cacae5 Deferred
#c8c8c8 Closed
|
SELECT
CASE WHEN status IN ('new','active') THEN
CASE type WHEN 'code' THEN '#f2dcdc' ELSE '#e8e8bd' END
WHEN status='fixed' THEN '#cfe8bd'
WHEN status='tested' THEN '#bde5d6'
WHEN status IN ('defer','review') THEN 'cacae5'
ELSE '#c8c8c8' END as 'bgcolor',
tn AS '#',
type AS 'Type',
status AS 'Status',
sdate(origtime) AS 'Created',
owner AS 'By',
subsystem AS 'Subsys',
sdate(changetime) AS 'Changed',
assignedto AS 'Assigned',
severity AS 'Svr',
priority AS 'Pri',
title AS 'Title',
description AS '_Description',
remarks AS '_Remarks'
FROM ticket
WHERE (changetime>now()-604800 OR status IN ('new','active'))
ORDER BY tn DESC
|