redmine の「作業時間を記録」のカスタムフィールドの値を集計する

gistfile1.sql

SELECT
  max(users.lastname) || max(users.firstname) as name,   sum(to_number(value, '0000000000000')), 
  max(custom_fields.name) as custom_field_name
FROM time_entries
  INNER JOIN (SELECT * FROM custom_values WHERE customized_type = 'TimeEntry') custom_values
    ON time_entries.id = customized_id
  INNER JOIN custom_fields
    ON custom_fields.id = custom_values.custom_field_id
  INNER JOIN users
    ON users.id = time_entries.user_id
WHERE 
    project_id = 1
GROUP BY user_id, custom_field_id
;

gistfile2.sql

SELECT
  users.lastname || users.firstname as name,          
  value,
  custom_fields.name as custom_field_name,
  time_entries.spent_on
FROM time_entries
  INNER JOIN (SELECT * FROM custom_values WHERE customized_type = 'TimeEntry') custom_values
    ON time_entries.id = customized_id 
  INNER JOIN custom_fields
    ON custom_fields.id = custom_values.custom_field_id
  INNER JOIN users
    ON users.id = time_entries.user_id
WHERE 
    project_id = 1
ORDER BY time_entries.spent_on
;

gistfile3.sql

SELECT
  max(name) as '氏名',
  sum(hours) as hours,
  sum(value_0) as '深夜勤務時間',
  sum(value_1) as '土曜・祝祭日勤務時間',
  sum(value_2) as '日曜A -土曜出勤なし',
  sum(value_3) as '日曜B -土曜出勤あり',
  b.spent_on
FROM time_entries
INNER JOIN (
SELECT
  max(name) as name,
  sum(value_0) as value_0,
  sum(value_1) as value_1,
  sum(value_2) as value_2,
  sum(value_3) as value_3,
  spent_on,
  time_entry_id
FROM(
SELECT
  CONCAT(users.lastname, users.firstname) as name, 
  case 
    when custom_field_id = 22 then value else 0
  end as value_0,
  case
    when custom_field_id = 23 then value else 0
  end as value_1,
  case
    when custom_field_id = 24 then value else 0
  end as value_2,
  case
    when custom_field_id = 27 then value else 0
  end as value_3,
  custom_fields.name as custom_field_name,
  time_entries.spent_on,
  custom_field_id,
  time_entries.id as time_entry_id
FROM time_entries
  INNER JOIN (SELECT * FROM custom_values WHERE customized_type = 'TimeEntry') custom_values
    ON time_entries.id = customized_id
  INNER JOIN custom_fields
    ON custom_fields.id = custom_values.custom_field_id
  INNER JOIN users
    ON users.id = time_entries.user_id
WHERE
    project_id = 213
) a
GROUP BY spent_on, time_entry_id
) b
ON time_entry_id = time_entries.id
WHERE project_id = 213
GROUP BY spent_on
;