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

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
| improve this answer | |
  • 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) – AlwaysLearning Jun 1 '18 at 16:16
  • Replace the INNER JOIN with a LEFT OUTER JOIN @AlwaysLearning – SQLRaptor Jun 1 '18 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 – AlwaysLearning Jun 1 '18 at 16:30
  • @AlwaysLearning - you are correct. need to adjust condition to allow it to WHERE (T1.UserID IS NULL,) OR .... – SQLRaptor Jun 1 '18 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! – AlwaysLearning Jun 1 '18 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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