Refactored the query based [1], but still contains inconsistent data and grouping is not only by the owner.
> > SELECT p.value AS __color__, > owner, > id as ticket, > summary, > t.type, > t.resolution, > t.time 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 to_char(to_timestamp(tc.time/1000000),'DDMMYYYY') > BETWEEN '$DATEBEGIN' AND '$DATEEND' > AND field='status' > AND newvalue='closed' > GROUP by p.value, t.owner, t.id, tc.time > ORDER BY tc.time DESC, t.time DESC, CAST(p.value AS integer), milestone, > t.type [1] http://stackoverflow.com/questions/13093221#13129262 @ivanelson []s 2013/6/2 Ivanelson Nunes <[email protected]> > 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? > > > So I want to specifically date the ticket was closed. > > > >> As you may guess, >> this has been asked before [1]. >> > > This query return some errors: > > ProgrammingError: column "p.value" must appear in the GROUP BY clause or be > used in an aggregate function LINE 2: SELECT p.value AS __color__, ^ > > > > >> >> 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.
