20. 月間作業実績一覧(SQL抜粋)
SELECT
substr(t.mon,1,4)||'年'||substr(t.mon,6,2)||'月 対応分' AS __group__,
t.mon as _発生分,
t.component,
(select count(*)
from ticket t1
where t.component=t1.component
and t1.type = '問合せ'
and t.mon=substr(date(t1.time, 'unixepoch', 'localtime'),1,7)
) as 問合せ,
ifnull(
(
select sum(CAST(newvalue as REAL))
from ticket_change work_time ,ticket t6
where work_time.ticket = t6.id
and work_time.field='hours'
and t.component = t6.component
and t6.type = '問合せ'
and t.mon = substr(date(work_time.time, 'unixepoch', 'localtime'),1,7)
),0
) as 問合せ時間,
(select count(*)
from ticket t2
where t.component=t2.component
and t2.type = '不具合'
and t.mon=substr(date(t2.time, 'unixepoch', 'localtime'),1,7)
) as 不具合,
～以下チケットのタイプ数分だけ繰り返し～
Shibuya.trac#11 2011/04/13
