CODEWITHSUNDEEP
sqlsql-server-2008
italian_homes
italian_homes

Reputation: 101

Tieing Two "Event Tables" Together with Timestamps SQL QUery

I have two DB tables in SQL Server. I would like to have a query result that will tie Operator Name to a row record in the another table. i.e. For each row record in "Table B", it would check to see which operator was logged in at the time of the event. The exception would be when no operator is logged in and thus there would be a null field in the result.

The screenshot below outlines the two tables and how the sample result would need to look. Any help would be greatly appreciated...I am out of my league here.

TABLEA

Operator Name| Log On Time |Log Out Time

Tom 10/01/2014 12:00 10/01/2014 12:09

Dick 10/01/2014 12:10 10/01/2014 12:35

Harry 10/01/2014 12:40 10/01/2014 12:45

TABLEB

Timestamp| Event

10/01/2014 12:03 A

10/01/2014 12:15 B

10/01/2014 12:36 A

REQUIRED QUERY RESULTS

Operator Name|Timestamp|Event Name

Tom 10/01/2014 12:03 A

Dick 10/01/2014 12:15 B

"No Operator" 10/01/2014 12:36 A

Upvotes: 1

Views: 98

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I would approach this with an explicit left join:

SELECT COALESCE(a.OperatorName, 'No Operator') as Operator, b.TIMESTAMP, b.EventName
FROM tableB b LEFT JOIN
     tableA a
     ON b.Timestamp BETWEEN a.LogOnTime and a.LogOutTime;

Upvotes: 2

Andrei Hirsu
Andrei Hirsu

Reputation: 696

Try an Outer Apply like this:

SELECT a.OperatorName
    ,b.TIMESTAMP
    ,b.EventName
FROM tableB b
OUTER APPLY (
    SELECT ISNULL(a.OperatorName,'No Operator')
    FROM tableA
    WHERE b.TIMESTAMP BETWEEN a.LogOnTime
            AND a.LogOutTime
    ) AS a

Upvotes: 2

Related Questions