-1

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.

cauthon
  • 161
  • 1
  • 10
  • Am I making this too complicated? – cauthon Apr 13 '19 at 03:49
  • Could you tell me the primary key of the user table? If there isn't a primary key in user table it will be a little complicated to get the result you want. I can give you result table almost like you want ,but exactly you want without primary key or more specific detail about your table structure. – 劉鎮瑲 Apr 13 '19 at 04:13
  • Primary key for user table is: user_id, userroleid, title, department, and team. Primary key for role table is: role_id, role_name – cauthon Apr 15 '19 at 15:39

2 Answers2

0

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
劉鎮瑲
  • 517
  • 9
  • 20
  • Primary key for user table is: user_id, userroleid, title, department, and team. Primary key for role table is: role_id, role_name. My earlier post wasn't giving me what I wanted either, so hopefully circling back around to see if your solution will work. – cauthon Apr 15 '19 at 15:41
-1

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!”

cauthon
  • 161
  • 1
  • 10
  • The above post was from a Reddit user named PrettyGoodMonkey – cauthon Apr 13 '19 at 14:22
  • Also found this posting after the fact: https://stackoverflow.com/questions/13513932/algorithm-to-detect-overlapping-periods?noredirect=1&lq=1 – cauthon Apr 13 '19 at 21:00