Using time interval in table for select in another

I am using a MySQL data base with 2 tables:

In one table I have BatchNum and Time_Stamp. In another I have ErrorCode and Time_Stamp.

My goal is to use timestamps in one table as the beginning and end of an interval within which I’d like to select in another table. I would like to select the beginning and end of intervals within which the BatchNum is constant.

CREATE TABLE Batch (BatchNum INT, Time_Stamp DATETIME);
INSERT INTO Batch VALUES (1,'2020-12-17 07:29:36'), (1, '2020-12-17 08:31:56'), (1, '2020-12-17 08:41:56'), (2, '2020-12-17 09:31:13'), (2, '2020-12-17 10:00:00'), (2, '2020-12-17 10:00:57'), (2, '2020-12-17 10:01:57'), (3, '2020-12-17 10:47:59'), (3, '2020-12-17 10:48:59'), (3, '2020-12-17 10:50:59');

CREATE TABLE Errors (ErrorCode INT, Time_Stamp DATETIME);
INSERT INTO Errors VALUES (10, '2020-12-17 07:29:35'), (11, '2020-12-17 07:30:00'), (12, '2020-12-17 07:30:35'), (10, '2020-12-17 07:30:40'), (22, '2020-12-17 10:01:45'), (23, '2020-12-17 10:48:00');

In my example below, I would like something like SELECT BatchNum , ErrorCode, Errors.Time_Stamp WHERE Erorrs.Time_Stamp BETWEEN beginning_of_batch and end_of_batch:

    +----------+-----------+---------------------+
    | BatchNum | ErrorCode |  Errors.Time_Stamp  |
    +----------+-----------+---------------------+
    |        1 |        11 | 2020-12-17 07:30:00 |
    |        1 |        12 | 2020-12-17 07:30:35 |
    |        1 |        10 | 2020-12-17 07:30:40 |
    |        2 |        22 | 2020-12-17 10:01:45 |
    |        3 |        23 | 2020-12-17 10:48:00 |
    +----------+-----------+---------------------+

I am using an answer from a previous question:

Select on value change

to find BatchNum changes but I don’t know how to include this in another select to get the ErrorCodes happening within the interval defined by BatchNum changes.

Answer

I think you want:

select b.*, e.error_code, e.time_stamp as error_timestamp
from (
    select b.*,
        lead(time_stamp) over(order by time_stamp) lead_time_stamp
    from batch b
) b
inner join errors e 
    on  e.time_stamp >= b.time_stamp
    and (e.time_stamp < b.lead_time_stamp or b.lead_time_stamp is null)

Advertisement

Answer