-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 You'd perform the same transformation, i.e.
concat(milestone, ' Release, due ', from_unixtime(m.due)) becomes milestone || ' Release, due ' || from_unixtime(m.due) I don't think from_unixtime is a standard function, though, so you may need to use a different method (depending on your database). For postgres, see this [1] post. For any other db, just google that db name + converting a unix timestamp into a date. Good luck, CM Lubinski http://cmlubinski.info [1] http://www.postgresonline.com/journal/index.php?/archives/3-Converting-from-Unix-Timestamp-to-PostgreSQL-Timestamp-or-Date.html bbew wrote: > Thanks CM. That worked for the owner one but how would I apply that > to: > > concat(milestone, ' Release, due > ',from_unixtime(m.due) > > Thanks again! Much appreciated. > > > On Mar 4, 3:25 pm, CM Lubinski <[email protected]> wrote: > If you are using postgres, the || operator will probably work. > > For example: > concat(owner, ' *') > becomes > owner || ' *' > > Hope that helps, > > CM Lubinskihttp://cmlubinski.info > > > > > > bbew wrote: >>>> Hey MC thanks so much for the response. I tried your report and got >>>> this error: >>>> "Report execution failed: no such function: concat" >>>> Any idea how I can work that function out of the report query? Would >>>> love to use yours - looks way better than mine. Sorry I'm a n00b. >>>> Thanks again, >>>> Rob >>>> On Mar 3, 6:48 am, MC <[email protected]> wrote: >>>>> You are ordering by *milestone* filed in *ticket* database, which is a >>>>> text field - the name of the milestone. >>>>> Details about each milestone are tracked in the *milestone* table, so >>>>> you have to join that table in your query, if you want due date. >>>>> Here's my report for active tickets per milestone: >>>>> SELECT p.value AS __color__, >>>>> (CASE t.milestone WHEN NULL THEN 'Unclassiffied' >>>>> WHEN '' THEN 'Without a Milestone' >>>>> ELSE concat(milestone, ' Release, due >>>>> ',from_unixtime(m.due)) END) AS __group__, >>>>> t.id AS ticket, t.summary, t.component, t.version as >>>>> _version, t.type AS type, priority, >>>>> (CASE t.status WHEN 'assigned' THEN concat(owner, ' *') ELSE >>>>> owner END) AS owner, >>>>> t.time AS created, >>>>> t.changetime AS modified, >>>>> t.description AS _description, >>>>> t.reporter AS _reporter >>>>> FROM ticket t >>>>> LEFT OUTER JOIN enum p ON p.name = t.priority AND p.type = >>>>> 'priority' >>>>> LEFT OUTER JOIN milestone m on t.milestone=m.name >>>>> WHERE status IN ('new', 'assigned', 'reopened') >>>>> ORDER BY (t.milestone IS NULL),m.due, p.value, t.type, time >>>>> On Mar 2, 2:47 pm, bbew <[email protected]> wrote: >>>>>> I have been banging on this report trying to get it to get it to order >>>>>> by milestone due date. Any help would be appreciated! >>>>>> SELECT p.value AS __color__, >>>>>> milestone||' Release' AS __group__, >>>>>> id AS ticket, summary, priority, owner, component, reporter, t.type >>>>>> AS type, >>>>>> time AS created, >>>>>> changetime AS _changetime, description AS _description, >>>>>> reporter AS _reporter >>>>>> FROM ticket t, enum p >>>>>> WHERE status <> 'closed' AND milestone <> ''AND p.name = t.priority >>>>>> AND p.type = 'priority' >>>>>> ORDER BYmilestone, p.value, severity, time -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFLkJfWfzi1OiZiJLARAtHpAJ0cwfxWgiuW8AerCR9dJleyUVZ7EQCdHpVF /j61y8eNLQ9xlxRl4o7+WQ8= =8C4w -----END PGP SIGNATURE----- -- You received this message because you are subscribed to the Google Groups "Trac Users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/trac-users?hl=en.
