Jump to content


Photo

Complex sql query problem


  • Please log in to reply
1 reply to this topic

#1 Gillesie

Gillesie
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 19 September 2005 - 11:07 AM

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.

#2 BVis

BVis
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 29 September 2005 - 10:05 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users