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!!!
Thanks,
Mark
