Logic for mapping 1 date range to another date range?

Question

I have one table of user history with a start and end date. I have a second table of role history with a start and end date.

I'm trying to take the user history table and break it up with the role history. Basically, I'm trying to map the role history to the user history.

For example:

Here is the user table

| person | title       | roleid | start     | end         |
|--------|-------------|--------|-----------|-------------|
| a      | VP          | 1      | 10/1/2017 | 10/31/2017  |
| a      | Director    | NULL   | 11/1/2017 | 11/25/2017  |
| a      | NULL        | 2      | 11/26/2017| 12/5/2017   |
| a      | President   | 3      | 12/6/2017 | 12/31/2017  |
| a      | Exec        | 3      | 01/01/2018| 12/31/2999  |

and the role history table

| roleid | role        | xxxxxx | start      | end         |
|--------|-------------|--------|------------|-------------|
| 1      | Champion    | x      | 10/05/2017 | 11/01/2017  |
| 2      | Nerd        | x      | 10/20/2017 | 12/31/2999  |
| 3      | Peon        | x      | 11/26/2017 | 12/15/2017  |
| 3      | King        | x      | 12/16/207  | 12/31/2017  |

I'm trying to get this type of result:

| person | title       | role_id | role   | start      | end         |
|--------|-------------|---------|--------|------------|-------------|
| a      | VP          | NULL    |NULL    | 10/01/2017 | 10/04/2017  |
| a      | VP          | 1       |champion| 10/05/2017 | 10/31/2017  |
| a      | Director    | NULL    |NULL    | 11/01/2017 | 11/25/2017  |
| a      | NULL        | 2       |nerd    | 11/26/2017 | 12/05/2017  |
| a      | President   | 3       |peon    | 12/06/2017 | 12/15/2017  |
| a      | President   | 3       |King    | 12/16/2017 | 12/31/2017  |
| a      | Exec        | 3       |King    | 01/01/2018 | 12/31/2999  |

I tried approaching this with multiple insert statements but I keep getting the logic wrong. Any help would be appreciated.

Answer 1

First: I create virtual primary key in the 'user table' by row_number function like below.

select *,ROW_NUMBER() OVER(order by person ,start) as vkey from 'user table'

Using above table i pretend it as 'temptable' to got which row by vkey we have to join 'role history table':

    select vkey,rn 
from(select * ,ROW_NUMBER() OVER (PARTITION BY person,roleid 
order by start) as rn) from 'temptable')temp where rn<=1

After got the vkey we can join 'role history table' to get the result you want.(I pretend above table as 'keytable'):

    select t1.person,t1.title,t1.role_id,t2.role,t1.start,t1.end from 'temptable' t1 
left join 'role history table' t2 on t1.role_id=t2.role_id 
and t1.vkey in (select vkey from 'keytable') order by t1.vkey

Answer 2

I found an answer that helped me.

“What you have here are two Type2 slowly changing dimensions (SCD). Normally when you're querying this type of data, you're using a single point in time to slice into the data by. But overlapping them?

This can seem pretty hard to do, but when you only have two tables to consider and you understand some of the logic, the pattern actually isn't too bad.

First, is testing overlapping ranges. Seriously, bookmark this answer. It's amazing and I've been going back to it for years!

You're going to apply the simplified expression from that answer on the join of the two tables: A.Start <= B.End AND A.End > B.Start

This will end up with cartesian result - that's fine. Each row's true effective range is the smallest overlapping window from the two tables:

, CASE WHEN A.Start > B.Start THEN A.Start ELSE B.Start END AS New_Start , CASE WHEN A.End < B.End THEN A.End ELSE B.End END AS New_End

And this should give you a new, denormalized Type2 SCD result set.

If you need to do more than two tables... well... I'd just script progressively joining two tables together, taking the result from the last join forward into the next one, applying the same pattern as above.

Hope that's what you were looking for!”