Jump to content

Complex sql query problem


Gillesie

Recommended Posts

Im having this rather complex query problem I'd like resolved.

1 table, 4 Columns,

tblErrors:

TramId(3) ErrorCode(1) Date (yyyy-mm-dd) Time(hh:mm:ss)

200 A 2005-01-01 12:15:01

200 A 2005-01-01 12:17:02

201 A 2005-01-01 13:00:15

201 B 2005-01-01 12:17:30

....

 

The key is all 4 columns together.

Basically the same error can occur only once at the same time (second) on a particular tram.

 

Say now I want the record count of everytime ErrorCode 'A' happens in a time interval of 3 minutes before ErrorCode 'B' occurs.

In my example the count would be 2. record 1 and 2 happen within that time interval of 3 minutes.

The reason I need this query, is to find a link between 2 errorcodes.

This table contains hundreds of thousands of records though.

Link to comment
Share on other sites

  • 2 weeks later...

Well, I've seen your post, and here is the solution...

 

With this table definition:

 

mysql> DESC errors;
+-----------+-----------------+------+-----+---------+-------+
| Field     | Type            | Null | Key | Default | Extra |
+-----------+-----------------+------+-----+---------+-------+
| TramId    | int(3) unsigned |      |     | 0       |       |
| ErrorCode | char(1)         | YES  |     | NULL    |       |
| Date      | datetime        | YES  |     | NULL    |       |
+-----------+-----------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

 

And this data inside:

 

mysql> SELECT * FROM errors;
+--------+-----------+---------------------+
| TramId | ErrorCode | Date                |
+--------+-----------+---------------------+
|    100 | A         | 2005-09-29 23:51:10 |
|    200 | B         | 2005-09-29 23:51:24 |
|    201 | A         | 2005-09-29 23:51:32 |
|    201 | A         | 2005-09-29 23:51:41 |
|    201 | B         | 2005-09-29 23:51:47 |
+--------+-----------+---------------------+
5 rows in set (0.00 sec)

 

We can use this query, which is very similar to yours...

 

SELECT
   e1.ErrorCode AS Error1,
   e1.Date AS Date1,
   e2.ErrorCode AS Error2,
   e2.Date AS Date2
FROM
   errors e1, errors e2
WHERE
   e1.Date <= e2.Date AND
   e1.Date > date_sub(e2.Date, INTERVAL 10 SECOND) AND
   e1.ErrorCode='A' AND
   e2.ErrorCode='B';

 

And you'll get this answer:

 

+--------+---------------------+--------+---------------------+
| Error1 | Date1               | Error2 | Date2               |
+--------+---------------------+--------+---------------------+
| A      | 2005-09-29 23:51:41 | B      | 2005-09-29 23:51:47 |
+--------+---------------------+--------+---------------------+

 

And that's all.

 

Regards,

 

BVis.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.