Re: [mysql] duplicating lines

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/mysql?unsub=lists@pantek.com