wu :: forums - Print Page


    
      
        wu :: forums
        (http://www.ocf.berkeley.edu/~wwu/cgi-bin/yabb/YaBB.cgi)
      

        riddles >> cs >> SQL Query (MySQL)
        
(Message started by: Sameer on Sep 5^th, 2007, 10:32pm)

Title: SQL Query (MySQL)
Post by Sameer on Sep 5^th, 2007, 10:32pm

Anyone good with SQL? I am having trouble with a query!!

I have a table as follows

Tablename: information
Columns: id (incremented everytime a row is inserted)
name: name of ticket
status: (values are open, fixed)
date: date status changed

E.g.
id/name/status (ignoring date for now)
1/aa/open
2/aa/fixed
3/aa/open <-- the same ticket can reopen
4/ab/open
10/aa/fixed <-- maybe other tickets have been updated between row with id 3 and 10

so you see we don't really have any correlation with id and ticket statuses.

Let's say I have these current rows:

id/name/status
40/aa/open
45/aa/open <-- somebody just updated the ticket will insert a row but status remains open
53/aa/fixed
65/aa/fixed <-- update

I want to detect the row the status changed from open to fixed.

The result I want is 53/aa/fixed using a query. Of course I want it over all the ticket names. But only the times when the status changed!! How do I go about doing this? (I put this question on MYSQL forum too; let's see if one of you gets this quickly!)

Title: Re: SQL Query (MySQL)
Post by TenaliRaman on Sep 6^th, 2007, 12:17am

Simple way. Write a small java app which connects to your database and retrieves all tuples and does your job.

Now if you want to do the same with sql, then i am a bit skeptical. But lets see :
1. Create view of all the tuples that are open (call it openView)
2. Create view of all the tuples that are fixed (call it fixedView)
3. Run a select query on the two views such that openView .id + 1 == fixedView.id

-- AI

Title: Re: SQL Query (MySQL)
Post by towr on Sep 6^th, 2007, 12:52am

It seems you want all open tickets with an id such that there is a closed ticket with the same name but a later id.

something like

SELECT id,name,status FROM information WHERE status='open' AND EXISTS (SELECT id as id2,name as name2,status as status2 FROM information WHERE name=name2 AND status='closed' AND id < id2);

See also
http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

Title: Re: SQL Query (MySQL)
Post by SMQ on Sep 6^th, 2007, 7:31am

Actually, I think he wants all records where the previous status for a ticket with the same name is different from the current status:

SELECT * FROM information a WHERE a.status <> (SELECT status FROM information b WHERE b.id = (SELECT MAX(id) FROM information c WHERE c.name = a.name AND c.id < a.id))

(The above query will also return the first entry for each ticket, by virtue of there being no previous row with the same name so b.status is NULL)

--SMQ

Title: Re: SQL Query (MySQL)
Post by towr on Sep 6^th, 2007, 7:48am

on 09/06/07 at 07:31:43, SMQ wrote:

Actually, I think he wants all records where the previous status for a ticket with the same name is different from the current status

Or something else still:

on 09/05/07 at 22:32:35, Sameer wrote:

I want to detect the row the status changed from open to fixed.

Only a small change though.

Title: Re: SQL Query (MySQL)
Post by SMQ on Sep 6^th, 2007, 7:54am

He also said:

on 09/05/07 at 22:32:35, Sameer wrote:

But only the times when the status changed

So it's not entirely clear whether he wants all status changes or just status changes from open to fixed. In the latter case, modify the above to SELECT * FROM information a WHERE a.status = 'fixed' AND a.status <> ...

--SMQ

Title: Re: SQL Query (MySQL)
Post by Sameer on Sep 6^th, 2007, 9:41am

on 09/06/07 at 07:54:19, SMQ wrote:

He also said:

So it's not entirely clear whether he wants all status changes or just status changes from open to fixed. In the latter case, modify the above to SELECT * FROM information a WHERE a.status = 'fixed' AND a.status <> ...

--SMQ

Ok here's some extra parameters for you!! The data ran is limited to 2 weeks. So check on 2 weeks from current date!! THat part is easy!!

In this two week period however you can have multiple status changes.. something like

10/open
20/open
30/close
40/close
50/close
60/open
70/close

In this case however I just want to count the status change from open to close once!!!

So consider a table like this

id/name/status

1/xx/open
2/xx/open
3/yy/open
4/zz/open
5/xx/open
6/xx/close
7/zz/close
8/zz/close
9/zz/close
10/zz/close
11/yy/open
12/yy/close
13/zz/close
14/xx/close
15/xx/open
16/xx/close

Output:
7/zz/close
12/yy/close
16/xx/close

Title: Re: SQL Query (MySQL)
Post by towr on Sep 6^th, 2007, 10:00am

So within the past two weeks, you want for each name, the last close not followed by another close of the same name?

I can't remember how to check dates properly though..

Title: Re: SQL Query (MySQL)
Post by SMQ on Sep 6^th, 2007, 10:08am

The MySQL syntax for dates may be a bit different, but:

SELECT * FROM information a WHERE a.date >= DATEADD(WEEK, -2, CURDATE()) AND a.status <> (SELECT status FROM information b WHERE b.id = (SELECT MAX(id) FROM information c WHERE c.name = a.name AND c.id < a.id)) AND NOT EXISTS (SELECT * FROM information d WHERE d.name = a.name AND d.status <> a.status AND d.id > a.id)

(and again, insert AND a.status = 'closed' if you're only interested in the closings and not all status changes.)

--SMQ