とた日記

_ 終了承認待ちチケットのレポート

何も考えずに進捗率(complete)を比較演算してしまうと進捗率の型が文字列型なので少し嵌ってしまいました．CAST(d.value as DECIMAL)などとすれば大丈夫でした．

SELECT (CASE owner WHEN 'somebody' THEN 'somebody' ELSE sa.value END) as __group__,
   id AS ticket,
   summary as '概要　　　　',
   lc.value as '終了日',
   d.value as '進捗率',
   t.type as 'タイプ　', 
   t.priority as '優先度',
   t.resolution as '解決方法',
   changetime AS _changetime, description AS _description,
   reporter AS _reporter,
   (CASE  WHEN lc.value = '' THEN 5
          WHEN lc.value < strftime('%Y/%m/%d','now') THEN 1
          WHEN lc.value < strftime('%Y/%m/%d','now', '7 day') THEN 2
          ELSE 3 END) AS __color__
  FROM ticket t
  LEFT JOIN session_attribute sa ON sa.sid = owner AND sa.name = 'name'
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_custom lc ON lc.ticket = t.id AND lc.name = 'last_closed'
  LEFT JOIN ticket_custom d ON d.ticket = t.id AND d.name = 'complete'
  WHERE status <> 'closed' AND CAST(d.value as DECIMAL) >= 90 AND lc.value <> ''
  ORDER BY owner, lc.value, milestone, t.type, time