From: Jay Pipes (jaymysql.com)
Date: Thu Sep 13 2007 - 12:26:48 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]