CODEWITHSUNDEEP
sqlsql-server-2008-r2timestamp
RobD64
RobD64

Reputation: 21

Select records where range between two timestamps is less 30 seconds

In Microsoft SQL Server 2008 R2, I am trying to query for records from one table, that records start and end times of job steps as timestamp columns, that are within thirty seconds of the timestamp column of an error log table. The error log table simply records the error event as an identity column and the date/time of the event as a timestamp, it does NOT contain any reference to the event that caused the error.

This is the query I wrote (I only need records within the last week, thus my first select statement, which works just fine):

SELECT 
    js.job_name, js.start_time, js.end_time 
FROM   
    job_step AS js 
WHERE  
    js.end_time > Dateadd(day, -8, Getdate()) 
    AND Datediff(ss, (SELECT el.err_timestamp 
                      FROM   error_log AS el), js.end_time) < 31

Until I add that second select, everything works fine, but when I add that select, I get this error:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , > >, >= or when the subquery is used as an expression.

Note--I am NOT a developer, I am a product analyst, but have a better than average grasp of sql than many non-developers; that said, please remember in your response that I am NOT a developer/coder.

Upvotes: 1

Views: 1445

Answers (3)

Tom H
Tom H

Reputation: 47464

This will give you all of the job steps that have an error within 30 seconds of it. It might give you multiple rows per job step if multiple errors fit that criteria, but I would expect that you would want that.

SELECT
    JS.job_name,
    JS.start_time,
    JS.end_time,
    EL.event AS potential_error_in_job
FROM
    dbo.Job_Step JS
INNER JOIN dbo.Error_Log EL ON
    EL.err_timestamp BETWEEN DATEADD(ss, -30, JS.start_time) AND DATEADD(ss, 30, JS.end_time)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would use exists for this purpoase:

WHERE js.end_time > Dateadd(day, -8, Getdate()) AND
      EXISTS (SELECT 1
              FROM error_log el
              WHERE DATEDIFF(second, el.err_timestamp, js.end_time) < 31
             );

I am guessing that you really want some sort of "between" logic. Perhaps:

WHERE js.end_time > Dateadd(day, -8, Getdate()) AND
      EXISTS (SELECT 1
              FROM error_log el
              WHERE DATEDIFF(second, el.err_timestamp, js.end_time) BETWEEN -30 AND 30 
             );

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

Either you need to correlate the sub-query or you should use TOP 1 with Order by

SELECT js.job_name, 
       js.start_time, 
       js.end_time 
FROM   job_step AS js 
WHERE  js.end_time > Dateadd(day, -8, Getdate()) 
       AND Datediff(ss, (SELECT TOP 1 el.err_timestamp 
                         FROM   error_log AS el
                         ORDER BY some_col), js.end_time) < 31

Upvotes: 1

Related Questions