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

: Redmine 3.0.0 のメールサーバーでGoogleApps SMTPサーバーを利用する

Redmineのメール通知サーバーとしてGoogleAppsを使用する方法です。 …

: Redmine 3.4にAgile Plugin をインストール

過去にも似たような記事を書いていますが、新しいバージョンのRedmine 3.4 …

: 「第14回RxTStudy(Redmine勉強会)」に行ってきました

土曜日の真っ昼間に半分以上が初参加者さんで80席が満席。それだけRedmine …

: Amazon LinuxにRedmine 環境構築(エラーと対応をそのまま記載版)

Amazon Linuxにgit + Redmineの環境を構築してみます。自 …

: RedmineにEasy Gantt プラグインをインストールしようとしたらRubyのバージョンが古いという事にひっかかったのでRubyもアップグレードした

RedmineにEasy Ganttプラグインをインストールしました。標準のガ …

: RedmineとGitLabを同じサーバにインストールする

Redmineでプロジェクトに設定できるGitはローカルのみ(Redmineと同 …

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

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

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

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

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

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

: Redmine 3.0.0でガントチャートの幅を変更してトラッカーを非表示にする

Redmineのガントチャートで親子チケットなど階層が深くなると肝心のチケット題 …

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

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

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

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

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

ad

ad

関連記事

Redmine 3.0.0 のメールサーバーでGoogleApps SMTPサーバーを利用する

Redmine 3.4にAgile Plugin をインストール

「第14回RxTStudy(Redmine勉強会)」に行ってきました

Amazon LinuxにRedmine 環境構築(エラーと対応をそのまま記載版)

RedmineにEasy Gantt プラグインをインストールしようとしたらRubyのバージョンが古いという事にひっかかったのでRubyもアップグレードした

RedmineとGitLabを同じサーバにインストールする

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

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

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

Redmine 3.0.0でガントチャートの幅を変更してトラッカーを非表示にする