Solved! Who interest the report. Query is between dates considering
the specific
date that the ticket was closed.
*Note that in my environment this report is raising an exception
caused by BlackMagicPlugin(Disable
solve), but I will discuss this in another issue or open a ticket in Trac-
hacks**.
*
SELECT
p.value AS __color__,
owner AS __group__,
t.id AS _Ticket,
summary,
t.type,
t.resolution,
to_char(to_timestamp(t.time/1000000),'DD/MM/YYYY') AS Created,
to_char(to_timestamp(MAX(tc.time)/1000000),'DD/MM/YYYY') AS Closed
FROM ticket_change tc
LEFT JOIN ticket t ON tc.ticket=id
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE tc.time >=
extract(epoch from
to_timestamp('$DATEBEGIN','DDMMYYYY'))::bigint*1000000
AND tc.time < extract(epoch from to_timestamp('$DATEEND','DDMMYYYY')
+ interval '1 day')::bigint*1000000
AND field='status'
AND newvalue='closed'
AND resolution='fixed'
GROUP by p.value, t.owner, t.id
ORDER BY t.owner, t.id
Thanks! Suggestions?!
@ivanelson
[]s
2013/6/2 Steffen Hoffmann <[email protected]>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 02.06.2013 09:19, Ivanelson Nunes wrote:
> > Hello,
> >
> > /I want to search by the closing date (ticket_change) and group the
> > results by owner./
> > /
> > /
> > /I'm using dynamic parameter to get dates. I really want the closing
> > date and so I'm using the table ticket_change./
> > /
> > /
> >
> > SELECT p.value AS __color__, t.owner AS __group__,
> > id as ticket, summary, t.type, t.resolution, t.time as
> > created,
> > to_char(to_timestamp(tc.time/1000000),'DD-MM-YYYY') as
> > closed
> > FROM ticket t, enum p, ticket_change tc
> > WHERE to_char(to_timestamp(tc.time/1000000),'DDMMYYYY') between
> > '$DATEBEGIN'
> > AND '$DATEEND'
> > AND p.name <http://p.name>=t.priority AND p.type='priority' AND
> > t.status = 'closed'
> > AND t.resolution='fixed'
> > AND tc.field='status' AND newvalue='closed'
> > ORDER BY t.owner
> >
> > The result is not correct.
>
> Because you're after the LAST closing date/time right? As you may guess,
> this has been asked before [1].
>
> Steffen Hoffmann
>
>
> [1] http://stackoverflow.com/questions/13093221#13129262
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAlGrXDIACgkQ31DJeiZFuHdi3QCgkVmTM1HvwtzJ4guHekWXfQR0
> mD0AoI/cK7avywk5jW0yBYnAm2+Wz2VR
> =OT+5
> -----END PGP SIGNATURE-----
>
> --
> You received this message because you are subscribed to the Google Groups
> "Trac Users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/trac-users?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
You received this message because you are subscribed to the Google Groups "Trac
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/trac-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
