Merging two type 2 dimension tables

Hi Everyone, I have ran into a problem while implementing a dw design.

I have an existing production database where users continously right into and it also simultaneously serves analytical purposes by storing history as startdate and enddates in all the tables having type 2 attributes. I dont know what to call it may be a fully normalized datamart !!!

We are trying to build a dw layer on this existing databse to help reporting. In the new design, many of the normalized type 2 dimesion tables are being merged into a single dimension table. So I have like 20 startdates and enddates for all the different dimensions now being brought into single table that dont correlate necessarily.

What is the best way to merge all these different startdates and enddates into one single range spanning across the entire time range inserting nulls/blanks for missing dimensions in some of the time periods.

let me give an example to make this clear

dim_Subjectsstudied_(old)
table1id stdID Name Subject StudyStartdtate StudyEnddate
1 1 Jon Maths 1990-07-01 1995-01-01
2 1 Jon English 1995-07-01 1999-01-01

dimsectionsenrolled_(old)
table2id stdID Name Section EnrolledStartdtate EnrolledEnddate
1 1 Jon 101 1990-01-01 2000-01-01

In the new design the data should look like this

Dimstudent
newtableID StdID Name Subject Section Rowstartdate Rowenddate
1 1 Jon NULL/NoSubject 101 1990-01-01 1990-06-30
2 1 Jon Maths 101 1990-07-01 1995-01-01
3 1 Jon NULL/NoSubject 101 1995-01-02 995-06-30
4 1 Jon English 101 1995-07-01 1999-01-01
3 1 Jon NULL/NoSubject 101 1999-01-02 2000-01-01


I hope I made my problem clear. I dont know if this a common problem but I am just writing t sql scripts to do this and its becoming too laborious when there are 20 startdates and enddates. Anyother way to do this effectively

Any suggestions would be greatly appreciated!!

Start by separating dimensions and facts. Someone being enrolled in something is a state. Facts reflect business events and states. The student, the course of study are all context for the state, those go into dimensions.

If you are trying to create a big flat table of everything that has ever happened, forget about it. Its a dead end.