0

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 |

1 Answer 1

1

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
5
  • Thanks, it is true I did answer my own question in a way and your solution does work however I mis-represented the scenario. I my case there are also event records in Table1 where there is no user that I wish to retain (added 2 records to the sample data for Table1) Jun 1, 2018 at 16:16
  • Replace the INNER JOIN with a LEFT OUTER JOIN @AlwaysLearning
    – SQLRaptor
    Jun 1, 2018 at 16:19
  • I did but my where condition is still conditional to T1.EventTimestamp joined to Table2 (User Status Table), in the case of the null user there is no record within Table 2 to join to thus it is excluded Jun 1, 2018 at 16:30
  • @AlwaysLearning - you are correct. need to adjust condition to allow it to WHERE (T1.UserID IS NULL,) OR ....
    – SQLRaptor
    Jun 1, 2018 at 16:44
  • Works great! Although I had to break this into 2 queries and add selection criteria via a sub query limiting the query range on Table2.StatusBeginTimestamp otherwise performance was very poor (contains tens of millions of records)... Thanks So Much! Jun 1, 2018 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.