Hi Craig,
would you mind posting the SHOW CREATE TABLE for the tables in question?
I'm having trouble determining what is the primary key for your
service ticket table...
Thanks,
-Jay
Weston, Craig (OFT) wrote:
Hello again,
I am having a duplication of results problem. I believe my
query (below) is giving me exactly what I deserve in writing it.
What it returns to me is a row for each status. What I would most want
would be a single row with the oldest status - IE the status datetime
that happened earliest.
What I am trying to do is determine when a service desk ticket first
enters any one of these three categories. I am not using distinct on
`thedata2`.`Source` as this does not effect the result set.
I have 2 tables.
One of the tables lists all the ticket information at time of the ticket
being closed. The other has an entry referenced by ticket number for
each time a ticket is touched or updated. So what I am trying to do is
identify the last time it was touched with the appropriate status
change.
Does anyone have any idea what I could do to eliminate the duplicate
with the oldest time? I am experimenting in the idea of a subquery but
can't think of anything else.
???
Thanks,
craig
SELECT
`thedata2`.`Source`,
`thedata1`.`Status`,
`thedata2`.`Priority`,
`thedata1`.`start_Time`,
`thedata1`.`Close_Time`,
`thedata1`.`workday`'cycletime'
FROM
`thedata2`
Inner Join `thedata1` ON `thedata1`.`Source` = `thedata2`.`SourceSR`
WHERE
(`thedata1`.`Status` like 'Resolved' OR
`thedata1`.`Status` like 'Restored' OR
`thedata1`.`Status` like 'Isolation')
and
`thedata2`.`Open_Time` BETWEEN '2007-02-01 00:00' AND '2007-08-31
23:59:59'
And
((`thedata2`.`Priority` = 1 and `thedata1`.`workday` > 14400)
OR
(`thedata2`.`Priority` = 2 and `thedata1`.`workday` > 86400)
or
(`thedata2`.`Priority` = 2 and `thedata1`.`workday` > 172800))
--------------------------------------------------------
This e-mail, including any attachments, may be confidential, privileged or
otherwise legally protected. It is intended only for the addressee. If you
received this e-mail in error or from someone who was not authorized to send it
to you, do not disseminate, copy or otherwise use this e-mail or its
attachments. Please notify the sender immediately by reply e-mail and delete
the e-mail from your system.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
