[fossil-users] Changed tickets report - getting previous values of a field
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: DateStatus ModifiedTitle 2013-01-12 Closed Dick 2013-01-12 Tested DickSome task 2013-01-11 DoneHarry Some task 2013-01-10 Started Harry Some task 2013-01-10 OpenDickSome 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? Thanks Steve ___ fossil-users mailing list fossil-users@lists.fossil-scm.org http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users
Re: [fossil-users] Changed tickets report - getting previous values of a field
On Fri, Jan 11, 2013 at 3:07 AM, Steve Landers st...@digitalsmarties.comwrote: 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: DateStatus ModifiedTitle 2013-01-12 Closed Dick 2013-01-12 Tested DickSome task 2013-01-11 DoneHarry Some task 2013-01-10 Started Harry Some task 2013-01-10 OpenDickSome 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 fossil-users@lists.fossil-scm.org http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users -- D. Richard Hipp d...@sqlite.org ___ fossil-users mailing list fossil-users@lists.fossil-scm.org http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users
Re: [fossil-users] Changed tickets report - getting previous values of a field
On Fri, Jan 11, 2013 at 8:37 AM, Richard Hipp d...@sqlite.org wrote: 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 Later today, I'll try to find time to enhance Fossil so that you can accomplish the above merely by creating a status column in the TICKETCHNG table and omitting the subquery. -- D. Richard Hipp d...@sqlite.org ___ fossil-users mailing list fossil-users@lists.fossil-scm.org http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users
Re: [fossil-users] Changed tickets report - getting previous values of a field
On Fri, Jan 11, 2013 at 3:07 AM, Steve Landers st...@digitalsmarties.comwrote: 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 How is the /timeline?y=t page is insufficient for this? 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: DateStatus ModifiedTitle 2013-01-12 Closed Dick 2013-01-12 Tested DickSome task 2013-01-11 DoneHarry Some task 2013-01-10 Started Harry Some task 2013-01-10 OpenDickSome 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? Thanks Steve ___ fossil-users mailing list fossil-users@lists.fossil-scm.org http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users -- D. Richard Hipp d...@sqlite.org ___ fossil-users mailing list fossil-users@lists.fossil-scm.org http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users
Re: [fossil-users] Changed tickets report - getting previous values of a field
On 11/01/2013, at 9:44 PM, Richard Hipp d...@sqlite.org wrote: 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 Excellent, thx. That shows me a way forward. Later today, I'll try to find time to enhance Fossil so that you can accomplish the above merely by creating a status column in the TICKETCHNG table and omitting the subquery. Even better. On Fri, Jan 11, 2013 at 3:07 AM, Steve Landers st...@digitalsmarties.com 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 How is the /timeline?y=t page is insufficient for this? It's not tabular and not easily configurable (color, number of entries or recent entries by time, order/sorting/format of columns, etc). Plus, I've added sortable columns via javascript. So, the information is there but not as accessible. Thanks again Richard. As always, much appreciated. Steve ___ fossil-users mailing list fossil-users@lists.fossil-scm.org http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users
