On Fri, Jan 11, 2013 at 3:07 AM, Steve Landers <[email protected]>wrote:
> I'm implementing a "Changed Tickets" report similar to that in Fossil's
> ticket system, the main difference is I want to show who made the change. I
> do this by joining ticket and ticketchg
>
> SELECT DISTINCT
> date(ticket.tkt_mtime),
> substr(tkt_uuid,1,10) AS '#',
> status,
> login,
> title
> FROM ticket LEFT OUTER JOIN ticketchng
> ON ticket.tkt_id = ticketchng.tkt_id
> ORDER BY ticket.tkt_mtime desc
>
> I'd like to do is show the status value for the particular ticket change
> (rather than the current value) so the report would return something like:
>
> Date Status Modified Title
> 2013-01-12 Closed Dick
> 2013-01-12 Tested Dick Some task
> 2013-01-11 Done Harry Some task
> 2013-01-10 Started Harry Some task
> 2013-01-10 Open Dick Some task
> 2013-01-09 New Tom Some task
>
> In this example, assume Tom is an end user, Dick is the tester and Harry
> is the developer.
>
> The above example returned Status Closed for all rows. Can anyone think of
> a convenient way to achieve the above report?
>
The "status" field is coming from the ticket table and thus shows the
latest status. To show the latest status at some point in the past, you
need a (I think) to first add a "status" field to your "ticketchng" table
definition. Then modify the query to be something like this (untested):
SELECT DISTINCT
date(ticket.tkt_mtime),
substr(tkt_uuid,1,10) AS '#',
(SELECT status FROM tkt_chng AS chng2
WHERE chng2.tkt_id = ckc1.tkt_id
AND chng2.tkt_time <= chng1.tkt_time
AND chng2.status IS NOT NULL
ORDER BY chng2.tkt_time DESC LIMIT 1),
login,
title
FROM ticket AS tkt1 LEFT OUTER JOIN ticketchng AS chng1
ON tkt1.tkt_id = chng1.tkt_id
ORDER BY tkt1.tkt_mtime desc
>
> Thanks
>
> Steve
> _______________________________________________
> fossil-users mailing list
> [email protected]
> http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users
>
--
D. Richard Hipp
[email protected]
_______________________________________________
fossil-users mailing list
[email protected]
http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users
