FogBugz Technical Support

A forum for technical support discussion related to Fogbugz.
The current FogBugz Knowledge Base can be found at http://help.fogcreek.com/fogbugz.

Posts by Fog Creek Employees are marked:

Documentation
Release Notes
Network Status

Pulling timesheet out of MySQL

Absent a nice little plug-in within FB, you can pull the timesheet information you want right out of FB using some quick SQL.  This is for MySQL; SQL Server and Access folks are on your own!

-- set your variables
SELECT '2009-08-01' INTO @month;
SELECT 'John Doe'  INTO @person;

-- monthly time worked, by case
SELECT COALESCE(`Case`, CONCAT('Total ', DATE_FORMAT(@month, '%M'))) AS 'Case',
      SEC_TO_TIME(SUM(TimeWorked)) AS 'Time'
  FROM (
        SELECT DATE_FORMAT(dtStart, '%U')            AS 'WeekWorked',
              DATE(dtStart)                        AS 'DateWorked',
              TIMESTAMPDIFF(SECOND, dtStart, dtEnd) AS 'TimeWorked',
              ixBug                                AS 'Case'
          FROM TimeInterval
          JOIN Person ON Person.ixPerson=TimeInterval.ixPerson
        WHERE TimeInterval.fDeleted=0 AND
              Person.sFullname=@person AND
              CONCAT(@month, ' 00:00:00') <= dtStart AND dtEnd <= CONCAT(LAST_DAY(@month), ' 11:59:59')
      ) AS T1
 GROUP BY `Case` ASC WITH ROLLUP
;

-- monthly time worked, by date
SELECT CASE
      WHEN WeekWorked IS NULL THEN CONCAT('Total ', DATE_FORMAT(@month, '%M'))
      WHEN DateWorked IS NULL THEN CONCAT('Total Week ', WeekWorked - DATE_FORMAT(@month, '%U'))
      ELSE DateWorked
      END AS 'Date',
      SEC_TO_TIME(SUM(TimeWorked)) AS 'Time',
      GROUP_CONCAT(DISTINCT `Case` ORDER BY `Case`) AS 'Cases'
  FROM (
        SELECT DATE_FORMAT(dtStart, '%U')            AS 'WeekWorked',
              DATE(dtStart)                        AS 'DateWorked',
              TIMESTAMPDIFF(SECOND, dtStart, dtEnd) AS 'TimeWorked',
              ixBug                                AS 'Case'
          FROM TimeInterval
          JOIN Person ON Person.ixPerson=TimeInterval.ixPerson
        WHERE TimeInterval.fDeleted=0 AND
              Person.sFullname=@person AND
              CONCAT(@month, ' 00:00:00') <= dtStart AND dtEnd <= CONCAT(LAST_DAY(@month), ' 11:59:59')
      ) AS T1
 GROUP BY WeekWorked, DateWorked ASC WITH ROLLUP
;


which produces (abbreviated for posting):
+--------------+----------+
| Case        | Time    |
+--------------+----------+
| 36          | 00:21:06 |
| 38          | 02:27:52 |
| 39          | 03:39:55 |
| 40          | 02:06:06 |
| 41          | 02:22:57 |
| 51          | 04:55:08 |
| 159          | 01:36:18 |
| 160          | 01:55:17 |
| 222          | 01:56:47 |
| 234          | 00:24:38 |
| 235          | 02:19:05 |
| 236          | 00:04:56 |
| 237          | 03:19:01 |
| 238          | 01:17:32 |
| 239          | 00:51:21 |
| 240          | 00:06:50 |
| 271          | 01:24:43 |
| 309          | 00:37:23 |
| 314          | 00:21:14 |
| 426          | 01:55:39 |
| 438          | 00:20:09 |
| Total August | 34:23:57 |
+--------------+----------+

+--------------+----------+------------------------------------------+
| Date        | Time    | Cases                                    |
+--------------+----------+------------------------------------------+
| 2009-08-03  | 01:43:53 | 38,51                                    |
| 2009-08-04  | 01:54:02 | 38                                      |
| 2009-08-05  | 01:14:31 | 38,39,51                                |
| 2009-08-06  | 01:55:10 | 39                                      |
| 2009-08-07  | 00:33:31 | 41                                      |
| Total Week 1 | 07:21:07 | 38,39,41,51                              |
| 2009-08-09  | 00:27:15 | 39                                      |
| 2009-08-10  | 02:17:29 | 39,41,51                                |
| 2009-08-11  | 04:05:09 | 40,159,160                              |
| 2009-08-12  | 01:08:49 | 40,51                                    |
| 2009-08-13  | 01:13:51 | 39,40,51                                |
| 2009-08-14  | 01:01:50 | 51,235,236                              |
| 2009-08-15  | 01:44:01 | 235,240                                  |
| Total Week 2 | 11:58:24 | 39,40,41,51,159,160,235,236,240          |
| 2009-08-17  | 02:10:57 | 51,234,239,271                          |
| 2009-08-18  | 02:34:08 | 51,237,239                              |
| 2009-08-19  | 03:02:10 | 237,238                                  |
| 2009-08-20  | 00:55:57 | 36,51                                    |
| 2009-08-21  | 00:15:00 | 51                                      |
| Total Week 3 | 08:58:12 | 36,51,234,237,238,239,271                |
| 2009-08-23  | 00:37:23 | 309                                      |
| 2009-08-24  | 00:34:16 | 51,314                                  |
| 2009-08-25  | 02:16:47 | 51,222                                  |
| 2009-08-27  | 02:17:39 | 51,426                                  |
| Total Week 4 | 05:46:05 | 51,222,309,314,426                      |
| 2009-08-30  | 00:20:09 | 438                                      |
| Total Week 5 | 00:20:09 | 438                                      |
| Total August | 34:23:57 | 36,38,39,40,41,51,159,160,222,234,235,..|
+--------------+----------+------------------------------------------+
Bishop Bettini Send private email
Friday, September 4, 2009
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz Bug Tracking and Evidence-Based Scheduling.