Redmineの作業時間、予定工数をSQLで抽出

2017/02/18 2017/02/20

プラグインとかでやればいいのかもなのですが、可視化はダッシュボードツールとかBIとかに任せた方が楽なのではと思い、Redmineから予定工数とか、実作業時間とか必要なデータを抽出してみました。
この抽出結果をre:dashとか使って可視化すればいいかと思います。

データベースはMySQLです。

月間プロジェクト別予定工数

SELECT
  date_format(a.due_date,'%Y年%m月') AS yearmonth,
  b.name,
  SUM(a.estimated_hours) AS hours
FROM
  redminedb.issues a
INNER JOIN redminedb.projects b
ON
  a.project_id=b.id
INNER JOIN redminedb.users c
ON
  a.assigned_to_id=c.id
AND c.id         IN ()
WHERE
  CAST(date_format(a.due_date,'%Y%m') AS signed)   >= 201604
AND CAST(date_format(a.due_date,'%Y%m') AS signed) <= 201703
GROUP BY
  yearmonth,
  b.name

SELECT

date_format(a.due_date,'%Y年%m月') AS yearmonth,

b.name,

SUM(a.estimated_hours) AS hours

FROM

redminedb.issues a

INNER JOIN redminedb.projects b

a.project_id=b.id

INNER JOIN redminedb.users c

a.assigned_to_id=c.id

AND c.id IN ()

WHERE

CAST(date_format(a.due_date,'%Y%m') AS signed) >= 201604

AND CAST(date_format(a.due_date,'%Y%m') AS signed) <= 201703

GROUP BY

yearmonth,

b.name

予定工数はissuesテーブルのestimated_hoursです。
担当者(assigned_to_id)で絞っています。
期日(due_date)ベースですのでチケットの粒度は細かめにして月をまたぐようなチケットがないようにしないとあまり意味がありません。
これを予定ですでに限界を迎えている月がないかを見るための可視化の元データとします。

月間プロジェクト別消化予定工数

SELECT
  date_format(a.due_date,'%Y年%m月') AS yearmonth,
  b.name,
  SUM(a.estimated_hours) AS hours
FROM
  redminedb.issues a
INNER JOIN redminedb.projects b
ON
  a.project_id=b.id
INNER JOIN redminedb.users c
ON
  a.assigned_to_id=c.id
AND c.id         IN ()
WHERE
  CAST(date_format(a.due_date,'%Y%m') AS signed)   >= 201604
AND CAST(date_format(a.due_date,'%Y%m') AS signed) <= 201703
AND a.status_id = 5
GROUP BY
  yearmonth,
  b.name

SELECT

date_format(a.due_date,'%Y年%m月') AS yearmonth,

b.name,

SUM(a.estimated_hours) AS hours

FROM

redminedb.issues a

INNER JOIN redminedb.projects b

a.project_id=b.id

INNER JOIN redminedb.users c

a.assigned_to_id=c.id

AND c.id IN ()

WHERE

CAST(date_format(a.due_date,'%Y%m') AS signed) >= 201604

AND CAST(date_format(a.due_date,'%Y%m') AS signed) <= 201703

AND a.status_id = 5

GROUP BY

yearmonth,

b.name

status_id=5 で完了チケットのみに絞ります。
これを予定に対しての進捗度合いを測るための可視化の元データとします。
見込みのボリュームのまま、消化度合いを測るために使います。

月間プロジェクト別作業実績

SELECT
  CONCAT(CAST(a.tyear as char),'年',LPAD(CAST(a.tmonth as char),2,'0'),'月') AS yearmonth,
  b.name,
  SUM(a.hours) AS hours
FROM
  redminedb.time_entries a
INNER JOIN redminedb.projects b
ON
  a.project_id=b.id
INNER JOIN redminedb.users c
ON
  a.user_id=c.id
AND c.id         IN ()
WHERE
  CAST(CONCAT(CAST(a.tyear as char),LPAD(CAST(a.tmonth as char),2,'0')) AS signed)   >= 201604
AND CAST(CONCAT(CAST(a.tyear as char),LPAD(CAST(a.tmonth as char),2,'0')) AS signed) <= 201703
GROUP BY
  yearmonth,
  b.name

SELECT

CONCAT(CAST(a.tyear as char),'年',LPAD(CAST(a.tmonth as char),2,'0'),'月') AS yearmonth,

b.name,

SUM(a.hours) AS hours

FROM

redminedb.time_entries a

INNER JOIN redminedb.projects b

a.project_id=b.id

INNER JOIN redminedb.users c

a.user_id=c.id

AND c.id IN ()

WHERE

CAST(CONCAT(CAST(a.tyear as char),LPAD(CAST(a.tmonth as char),2,'0')) AS signed) >= 201604

AND CAST(CONCAT(CAST(a.tyear as char),LPAD(CAST(a.tmonth as char),2,'0')) AS signed) <= 201703

GROUP BY

yearmonth,

b.name

作業時間はtime_entriesテーブルのhoursです。
これを工数見積に対して実際どうであったかの予実の指標として使います。

他はtime_entriesの作業種類を使って週や月で何の作業(開発、運用、打ち合わせなど)に時間をかけているのかとか、生産性を高めるために振り返るワークログとして使えればと思っています。

最後までお読みいただきましてありがとうございました！

「AWS認定資格試験テキスト＆問題集　AWS認定ソリューションアーキテクト - プロフェッショナル改訂第2版」という本を書きました。

「AWS認定資格試験テキスト AWS認定クラウドプラクティショナー改訂第3版」という本を書きました。

「ポケットスタディ AWS認定デベロッパーアソシエイト［DVA-C02対応］」という本を書きました。

「要点整理から攻略するAWS認定ソリューションアーキテクト-アソシエイト」という本を書きました。

「AWSではじめるLinux入門ガイド」という本を書きました。

@yamamanx

開発ベンダー5年、ユーザ企業システム部門通算9年、ITインストラクター5年目でプロトタイプビルダーもやりだしたSoftware Engineerです。
質問はコメントかSNSなどからお気軽にどうぞ。
出来る限りなるべく答えます。

このブログの内容/発言の一切は個人の見解であり、所属する組織とは関係ありません。
このブログは経験したことなどの共有を目的としており、手順や結果などを保証するものではありません。
ご参考にされる際は、読者様自身のご判断にてご対応をお願いいたします。

また、勉強会やイベントのレポートは自分が気になったことをメモしたり、聞いて思ったことを書いていますので、登壇者の意見や発表内容ではありません。

- Redmine Redmine

: CentOS 6.5にRedmineをインストールするためにrubyをインストールする

とりあえずgitは入っている前提で [bash] # cd /opt # git …

: AWSのAmazon LinuxにGitマスターサーバをインストールしてRedmineリポジトリブラウザで見る

Amazon LinuxにGitをインストールする Gitをインストールして自動 …

: Redmine3.0.0に CSV Importerプラグインをインストールする

バグ管理や開発の現場だけではなく進捗管理などにも重宝出来るRedmine。その …

: Redmine 3.0.0 にLocal Avatar プラグインをインストールしてユーザーがアバターを簡単に変更出来るようにする

Redmineのアバターは標準でGravater採用なのですが、エンドユーザーに …

: Redmine プラグイン開発チュートリアルをやってみる

Redmine 3.0環境でプラグインを作成するチュートリアルをやってみました。 …

: Redmine起動時にPassengerで「Could not spawn process for application」エラー

AWSで起動しているRedmineのEC2をt2.microからt2.small …

: Redmine 3.0.0に CRMプラグインをインストールする

Redmine CRMプラグインのFreeのLightバージョンを使ってみる事に …

: Amzon Linux のApacheでRedmineとWordPressをバーチャルホストで共存する

EC2とRDSを節約しようと思いまして、Redmineを動かしてるとこに検証用W …

: redmine_omniauth_googleプラグインをRedmine3.0.0で動くようにする

redmine_omniauth_googleプラグインをインストールしたところ …

: Redmine Agileプラグインをバージョンアップインストールする

Redmine 3.0.0でRedmineCRMのAgileプラグインをインスト …

PREV: DS18B20センサー+Raspberry Piで取得した温度をAmazon Kinesis FirehoseからS3へ格納してAthenaでクエリーしたのをQuickSightで可視化する
NEXT: kintone webhookからAWS API Gateway - Lambdaを実行しレコードの値を渡す

Redmineの作業時間、予定工数をSQLで抽出

月間プロジェクト別予定工数

月間プロジェクト別消化予定工数

月間プロジェクト別作業実績

ad

ad

関連記事

CentOS 6.5にRedmineをインストールするためにrubyをインストールする

AWSのAmazon LinuxにGitマスターサーバをインストールしてRedmineリポジトリブラウザで見る

Redmine3.0.0に CSV Importerプラグインをインストールする

Redmine 3.0.0 にLocal Avatar プラグインをインストールしてユーザーがアバターを簡単に変更出来るようにする

Redmine プラグイン開発チュートリアルをやってみる

Redmine起動時にPassengerで「Could not spawn process for application」エラー

Redmine 3.0.0に CRMプラグインをインストールする

Amzon Linux のApacheでRedmineとWordPressをバーチャルホストで共存する

redmine_omniauth_googleプラグインをRedmine3.0.0で動くようにする

Redmine Agileプラグインをバージョンアップインストールする