2007-06-05

Week 10 practice plan

  1. Juggling and passing warmup: Passes must be at least 40 yards long. Who can accurately pass the farthest? Add long throw-ins and correct throw-in technique

  2. 2v1: The 1 starts with the ball and tries to maintain possession for as long as possible. Count how long you can hold the ball--1-1000, 2-1000, 3-1000, ... Who held the ball longest?

  3. Throw in wall pass

  4. Four-squares passing

  5. 6 goal width and depth game

  6. 8v8: Points count only if the entire attacking team is in the last 2/3 of the field. Quick attack reward: 2 points if the defending team isn't entirely in their defensive 2/3 of the field. Possibly leave the goals in the corner from the 6 goal game to reward crosses.

2007-06-01

Jira time tracking report

We use Jira religiously as our issue tracking tool. Whenever we work on an issue, we add comments and log the amount of time we spent. At the end of the week, we want to run a report that tells us who worked on what, what happened, and how much time was spent.

Jira doesn't provide this kind of report out of the box, and there aren't any plugins available that do it. Short of writing my own plugin, here's some SQL code to generate these kinds of reports.

First, what did I work on this week? Here's the query and its output, with work time reported in seconds:

mysql> select i.pkey, i.summary, a.created, a.actionnum
-> from jiraissue as i, jiraaction as a
-> where a.actiontype in ('comment', 'worklog')
-> and a.created >= '2007-05-28 00:00:00'
-> and a.created < '2007-06-03 24:00:00'
-> and a.author='richard'
-> and i.id = a.issueid
-> order by i.pkey;
+---------+------------------------------------------------------------------+---------------------+-----------+
| pkey | summary | created | actionnum |
+---------+------------------------------------------------------------------+---------------------+-----------+
| ABC-150 | where does the user go if they select "no" | 2007-05-29 15:47:31 | NULL |
| ABC-188 | search results page button/results | 2007-05-29 15:46:27 | NULL |
| ABC-284 | button follows form not aligned to bottom of screen as in mockup | 2007-05-29 15:44:37 | NULL |
| DEF-26 | SecurityException | 2007-05-30 15:54:03 | NULL |
| DEF-63 | Exit client | 2007-05-30 15:49:43 | NULL |
| DEF-64 | We need a link | 2007-05-30 15:52:50 | NULL |
| GHI-379 | user agent | 2007-05-30 11:05:51 | NULL |
| JKL-1 | Be a good boy. | 2007-05-31 15:15:44 | 3600 |
| JKL-3 | Try to have fun. | 2007-05-31 13:18:28 | 10800 |
| JKL-3 | Try to have fun. | 2007-05-31 13:18:42 | 900 |
| MNO-1 | Content Integration with Customer | 2007-05-30 16:05:12 | NULL |
| MNO-1 | Content Integration with Customer | 2007-05-30 16:08:04 | NULL |
+---------+------------------------------------------------------------------+---------------------+-----------+

Next, what did everyone work on this week? Here's the query and its output:

mysql> select a.author, i.pkey, i.summary, a.created, a.actionnum
-> from jiraissue as i, jiraaction as a
-> where a.actiontype in ('comment', 'worklog')
-> and a.created >= '2007-05-28 00:00:00'
-> and a.created < '2007-06-03 24:00:00'
-> and i.id = a.issueid
-> order by a.author, i.pkey;
+----------+---------+------------------------------------------------------------------+---------------------+-----------+
| author | pkey | summary | created | actionnum |
+----------+---------+------------------------------------------------------------------+---------------------+-----------+
| alice | ABC-322 | ant script has targets that seem to crash some of us | 2007-05-29 14:46:42 | NULL |
| alice | ABC-322 | ant script has targets that seem to crash some of us | 2007-05-29 14:48:14 | NULL |
| alice | ABC-336 | "buy" link | 2007-05-31 12:42:31 | NULL |
| bob | PQR-2 | Improve User Interface of the Log Viewer | 2007-05-29 20:07:02 | 10800 |
| bob | PQR-23 | Uninformative error message | 2007-05-29 17:36:55 | NULL |
| bob | STU-62 | Error when opening | 2007-05-28 19:07:58 | 18000 |
| carol | ABC-12 | Featured Items | 2007-05-29 11:09:28 | 39600 |
| carol | ABC-264 | missing message | 2007-05-29 11:08:47 | 3600 |
| carol | ABC-271 | breadcrumb | 2007-05-29 11:08:20 | 3600 |
| dan | ABC-284 | button follows form not aligned to bottom of screen as in mockup | 2007-05-29 16:14:53 | NULL |
| dan | DEF-10 | Port | 2007-05-30 11:30:24 | NULL |
| dan | DEF-26 | SecurityException | 2007-05-30 11:25:20 | NULL |
+----------+---------+------------------------------------------------------------------+---------------------+-----------+

Finally, how much time did people log this week? Here's the query and its output.

mysql> select author, sum(actionnum)
-> from jiraaction
-> where actiontype = 'worklog'
-> and created >= '2007-05-28 00:00:00'
-> and created < '2007-06-03 24:00:00'
-> group by author
-> order by author;
+----------+----------------+
| author | sum(actionnum) |
+----------+----------------+
| bob | 75600 |
| carol | 88800 |
| emil | 93600 |
| francine | 15300 |
| gary | 54000 |
+----------+----------------+

The next step is to package this as a plugin. Want to help? Let me know.

LinkWithin

Related Posts with Thumbnails