You are the man CM! I went with this:
SELECT p.value AS __color__,
(CASE t.milestone WHEN NULL THEN 'Unclassiffied'
WHEN '' THEN 'Without a Milestone'
ELSE
milestone || ' Release, due ' || (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 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
I still have the unix 1970 second timestamp displayed as the due date
but all of our milestones are named by date so it doesn't matter.
Thanks again!
On Mar 4, 9:34 pm, CM Lubinski <[email protected]> wrote:
> -----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-Convertin...
>
>
>
>
>
> 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.
