-- ## 17: Ticket Hours Grouped By Milestone ## -- -- Reports Must Be Accessed From the Management Screen SELECT __color__, __group__, __style__, ticket, summary, component ,version, severity, __milestone__, status, owner, Estimated_work, Total_work, billable, _ord FROM ( SELECT p.value AS __color__, t.milestone AS __group__, '' as __style__, t.id AS ticket, summary AS summary, -- ## Break line here component,version, severity, milestone as __milestone__, status, owner, CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 ELSE CAST( EstimatedHours.value AS DECIMAL ) END as Estimated_work, CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, CASE WHEN billable.value = '1' THEN 'Y' else 'N' END as billable, time AS created, changetime AS modified, -- ## Dates are formatted description AS _description_, -- ## Uses a full row changetime AS _changetime, reporter AS _reporter, 0 as _ord FROM ticket as t LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority' LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' AND EstimatedHours.Ticket = t.Id LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' AND totalhours.Ticket = t.Id LEFT JOIN ticket_custom as billable ON billable.name='billable' AND billable.Ticket = t.Id WHERE t.status IN ($REOPENED, $ASSIGNED, $CLOSED, $NEW) AND billable.value in ($BILLABLE, $UNBILLABLE) UNION SELECT '1' AS __color__, t.milestone AS __group__, 'background-color:#DFE;' as __style__, 0 as ticket, 'Total work' AS summary, NULL as component,NULL as version, NULL as severity, t.milestone as __milestone__, 'Time Remaining: ' as status, CAST( SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 ELSE CAST( EstimatedHours.value AS DECIMAL ) END) - SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 ELSE CAST( totalhours.value AS DECIMAL ) END) AS CHAR(512)) as owner, SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0 ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work, SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0 ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work, NULL as billable, NULL as created, NULL as modified, -- ## Dates are formatted NULL AS _description_, NULL AS _changetime, NULL AS _reporter ,1 as _ord FROM ticket as t LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority' LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' AND EstimatedHours.Ticket = t.Id LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' AND totalhours.Ticket = t.Id LEFT JOIN ticket_custom as billable ON billable.name='billable' AND billable.Ticket = t.Id WHERE t.status IN ($REOPENED, $ASSIGNED, $CLOSED, $NEW) AND billable.value in ($BILLABLE, $UNBILLABLE) GROUP BY t.milestone ) as tbl ORDER BY __milestone__, _ord ASC, ticket