On Fri, 2006-03-10 at 10:09 +1300, Felix Collins wrote:
> Tomas Rudén wrote:
> > I really appreciate the current Report module. The SQL-query together
> > with formatting really gives us the ability to create reports according
> > to customers demand.
> >
>
> We do a complex query <beware the following SQL may make your brain
> hurt> involving joining to the ticket_custom table and summing fields to
> generate time reports. How on earth is the query module going to
> replace that!
Well, I don't think anything's set in stone, but the report module is
rather a PITA and I think the query module should be encouraged wherever
possible.
Also, the following report may cause less aneurysms (disclaimer: I
haven't tested it since I don't use those fields, but it should be close
to what you want):
SELECT t.owner AS Developer,
SUM(CASE WHEN currest.value not in ('', '0') THEN currest
WHEN est.value not in ('', '0') THEN est END)
AS Estimate_On_Remaining_Tickets,
SUM(cumul.value) AS Cumulative_Time_On_Remaining_Tickets
FROM ticket AS t
LEFT JOIN ticket_custom est on estimate.ticket = t.id
and name = 'estimatehours'
LEFT JOIN ticket_custom currest currest.ticket = t.id
and name = 'currentestimatehours'
LEFT JOIN ticket_custom cumul on cumul.ticket_custom = t.id
and name = 'cumulativehours'
WHERE t.status IN ('new', 'assigned', 'reopened')
GROUP BY Developer
--
Matthew Good <[EMAIL PROTECTED]>
_______________________________________________
Trac mailing list
[email protected]
http://lists.edgewall.com/mailman/listinfo/trac
