I have two tables of mixed Granularity and mixed date range and am struggling to figure out the best way to model them.
Table 1 is a Transactions table. This Table has a Transaction Date which can be any date and has data back to 2010. See below for all Transactions by Owner = A ID = 1A. Note there are Months When there are no Transactions
Owner
ID
TransactionDate
A
1A
1/1/2010
A
1A
3/2/2011
A
1A
6/16/2012
A
1A
7/20/2013
A
1A
4/29/2014
A
1A
10/30/2015
A
1A
12/4/2016
A
1A
5/5/2017
A
1A
6/6/2018
My second tables is a "Positions Table". The Positions table has a Month End Date and goes back to 2015 Below is an example of Positions for Owner = A and ID = 1A
Owner
ID
MonthEndDate
Value
A
1A
1/1/2013
1
A
1A
2/1/2013
2
A
1A
3/1/2013
1
A
1A
4/1/2013
3
A
1A
5/1/2013
4
A
1A
6/1/2013
5
A
1A
7/1/2013
6
I want to Transactions by Owner & ID & MonthEnd(TransactionDate) to its Owner & ID & MonthEndDate in the Positions table. I am having issues because of the mixed Date Ranges between the tables.
If I select an ID from the Positions table I only get its Transactions going back to 2013.
I thought about creating a Concatenated Fact table but I then ran into the issue of if I select a TransactionDate, I wont see the value from Positions for the Month End of that Transaction.
Any suggestions on how best model this would be much appreciated!!!
