I am currently creating a data model with a Link Table. In this data I want to be able to link to dimension tables with each other.
I have created two tables one with the distinct values that I want to create records for in the Master Link Table and one table with the dates that I want to create records for.
It looks something like this.
Company Link Table:
Company Number, Sales Person ID
Company 1, Salesman 1
Company 1, Salesman 2
Company 2, Salesman 1
Company 30, Salesman 15
Now I want to create one table that has all possible values for the distinct values of Company Link Table and Date Range.
Company Number, Sales Person ID, Date
Company 1, Salesman 1, 2014-01-01
Company 1, Salesman 2, 2014-01-01
Company 1, Salesman 1, 2014-02-01
Company 1, Salesman 2, 2014-02-01
Company 2, Salesman 1, 2014-01-01
Company 30, Salesman 15, 2015-01-01
I don’t know how to make the last step where I join the two table together and creates the records that are missing. I can’t use a join or concatenate since I don’t have values that are shared in both tables.
Cool, it was that easy. I knew I wanted that table as the result. I didnt know that it was that easy to just use the normal join. I had problem trying to use the joins that it didnt create the records where I didnt have value.