Sql-server – Selecting Records In SQL Server From Joined Table Where Timestamp Occurs Between Begin/End Range

sql server

I have 2 tables

Table1 contains RecordID, UserID, Event, EventTimestamp

Table2 contains UserID, UserStatus, UserStatusSubgroup, StatusBeginTimestamp, StatusEndTimestamp

Table1 –SQL Server 2014 Table–

RecordID [char(18)]
UserID [nvarchar(50)]
Event [nvarchar(20)]
EventTimestamp [DateTime2(3)]

Table2 –SQL Server 2014 Table–

UserID [nvarchar(50)]
UserStatus [nvarchar(10)]
UserStatusSubgroup [nvarchar(20)]
StatusBeginTimestamp [DateTime2(3)]
StatusEndTimestamp [DateTime2(3)]

Primary Key is UserID

For each RecordID in Table1 there can be many Events performed by the UserID with the EventTimestamp recorded

I wish to join to Table2 to retreive Table2.UserStatus and Table2.UserStatusSubgroup where Table1.EventTimestamp occurs between Table2.StatusBeginTimestamp and Table2.StatusEndTimestamp

How can I do this?

--Setup demo data
Declare @Table1 table
    (RecordID int, UserID varchar(5), Event varchar(20), EventTimestamp DateTime2(3))
;

INSERT INTO @Table1
    (RecordID, UserID, Event, EventTimestamp)
VALUES
    (123456, 'Bob', 'EventA', '2018-05-30 19:01:29.683'),
    (123456, 'Bob', 'EventB', '2018-05-30 19:02:25.893'),
    (123456, 'Bob', 'EventC', '2018-05-30 19:03:15.323'),
    (123456, 'Bob', 'EventD', '2018-05-30 19:04:45.013'),
    (123456, 'Bob', 'EventE', '2018-05-30 19:05:12.993'),
    (123456, 'Null', 'EventF', '2018-05-30 19:06:00.999'),
    (123456, 'Null', 'EventG', '2018-05-30 19:07:00.002')

;

| RecordID  | UserID | Event      | EventTimestamp          |
|-----------|--------|------------|-------------------------|
| 123456    | Bob    | EventA     | 2018-05-30 19:01:29.683 |
| 123456    | Bob    | EventB     | 2018-05-30 19:02:25.893 |
| 123456    | Bob    | EventC     | 2018-05-30 19:03:15.323 |
| 123456    | Bob    | EventD     | 2018-05-30 19:04:45.013 |
| 123456    | Bob    | EventE     | 2018-05-30 19:05:12.993 |
| 123456    | Null   | EventF     | 2018-05-30 19:06:00.999 |
| 123456    | Null   | EventG     | 2018-05-30 19:07:00.002 |

--Setup demo data
Declare @Table2 table
    (UserID varchar(5), UserStatus varchar(10), UserStatusSubgroup varchar(20), StatusBeginTimestamp DateTime2(3), StatusEndTimestamp DateTime2(3)

;

INSERT INTO @Table2
    (UserID, UserStatus, UserStatusSubgroup, StatusBeginTimestamp, StatusEndTimestamp)
VALUES
    ('Bob', 'StatusA', 'SubStatus1', '2018-05-30 19:00:00.000', '2018-05-30 19:03:00.000'),
    ('Bob', 'StatusB', 'SubStatus2', '2018-05-30 19:03:00.000', '2018-05-30 19:04:00.000'),
    ('Bob', 'StatusC', 'SubStatus3', '2018-05-30 19:04:00.000', '2018-05-30 19:06:00.000')
;

| UserID | UserStatus | UserStatusSubgroup   | StatusBeginTimestamp    |  StatusEndTimestamp     |
|--------|------------|----------------------|-------------------------|-------------------------|
| Bob    | StatusA    | SubStatus1           | 2018-05-30 19:00:00.000 | 2018-05-30 19:03:00.000 |
| Bob    | StatusB    | SubStatus2           | 2018-05-30 19:03:00.000 | 2018-05-30 19:04:00.000 |
| Bob    | StatusC    | SubStatus3           | 2018-05-30 19:04:00.000 | 2018-05-30 19:06:00.000 |

Best Answer

You spelled out your solution:

"I wish to join to Table2 to retreive Table2.UserStatus and Table2.UserStatusSubgroup where Table1.EventTimestamp occurs between Table2.StatusBeginTimestamp and Table2.StatusEndTimestamp"

    SELECT T1.*, T2.UserStatus, T2.UserStatusSubgroup
    FROM @Table1 AS T1
        INNER JOIN -- or LEFT OUTER JOIN to retain the rows with null userid
        @Table2 AS T2
        ON T1.UserID = T2.UserID
    WHERE /*(T1.UserID IS NULL) OR -- to retain rows with null userid  */
          T1.EventTimestamp BETWEEN T2.StatusBeginTimestamp AND T2.StatusEndTimestamp
Related Question