-- ## 6: All Tickets by Milestone  (including closed) ## --

SELECT p.value AS __color__,
   'Milestone: ' || (CASE
                       WHEN milestone='' THEN 'none'
                       ELSE milestone
                     END) AS __group__,
   (CASE status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
    END) AS __style__,
   id AS ticket, summary, component, version, t.type AS type, owner,
   status, resolution, changetime AS modified,
   time AS _time, reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  ORDER BY (milestone='') ASC, milestone, (status = 'closed'), 
        (CASE status WHEN 'closed' THEN resolution ELSE p.value END),
        (CASE
           WHEN t.type='defect' THEN 'aaa'
           WHEN t.type='task' THEN 'aab'
           WHEN t.type='enhancement' THEN 'aac'
           ELSE t.type
         END), changetime DESC