Hi,
I have narrowed the query - I just want to return the FIRST instance of
each ticket, ie the Source. Here is the table:
CREATE TABLE `Thedata1` (
`Source` varchar(15) collate latin1_general_ci NOT NULL default '',
`Close_Time` datetime NOT NULL default '0000-00-00 00:00:00',
`start_Time` datetime default NULL, COMMENT 'correct time in Seconds'
`start_Time2` datetime default NULL COMMENT 'Original Time in
Seconds',
`Original_Cycle_Time` decimal(11,4) default NULL,
`workday` double(20,0) default NULL,
PRIMARY KEY (`Source`,`start_Time2`,`Close_Time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
My best guess on how to do this would be a self join?
select
`a1`.`Source,
If(`a1`.`workday` < `a2`.`workday`, `a1`.`workday`,`a2`.`workday`)
from `Thedata1` as `a1` inner join `Thedata1` as `a2`
on `a1`.`Source` = `a2`.`table1` and `a1`.`workday` > `a2`.`workday`
There can be more than 2 duplicates, and occasionally no duplicates.
I really am not sure why this isn't working. Can anyone offer an
insight?
Thank you,
Craig
-----Original Message-----
From: Jay Pipes [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 13, 2007 1:27 PM
To: Weston, Craig (OFT)
Cc: mysql@lists.mysql.com
Subject: 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.
