SQL - Combining Fact and Dimension Data in Date Ranges
I have two tables in my sql server database that I want to join based on their date range. This data is confidential, I will try to keep it as clear as possible, although I got to disclose the data, so I hope you are ok with that.
One of the tables is transactional data, which represents the use of one specific entity that will be billed monthly, see a preview of this table below.
+------------+------------+------------+------------+------------------+
| Object1 ID | Object2 ID | tFrom | tTo | Transaction Cost |
+------------+------------+------------+------------+------------------+
| 54324 | AAA | 01/01/2017 | 01/01/2017 | 15,45 € |
| 54324 | AAA | 02/01/2017 | 02/01/2017 | 20,00 € |
| 54324 | AAA | | 15/01/2017 | 3,00 € |
| 54324 | AAA | | 02/02/2017 | 2,00 € |
| 56767 | BBB | | 12/12/2017 | 1,78 € |
| 34567 | BBB | 02/01/2017 | 02/01/2017 | 40,00 € |
| 34567 | BBB | 04/01/2017 | 04/01/2017 | 30,00 € |
| 34567 | BBB | | 16/01/2017 | 9,00 € |
| 34567 | BBB | 31/01/2017 | 31/01/2017 | 45,00 € |
| 85432 | CCC | 31/12/2016 | 31/12/2016 | 23,00 € |
| 85432 | CCC | 02/01/2017 | 02/01/2017 | 12,00 € |
+------------+------------+------------+------------+------------------+
As you can see, there are two objects in the table - this is because the use of the billed object depends on the other and there may be different allocations of objects1 and object2 monthly, although toggles between objects are not that common.
My sizing chart looks at 3 types of services (one of the witches is presented in the table above) and contains several custom sizes (I only presented 2 of them, so the table will not be too big). These sizes are subject to change monthly. See a preview of this table below:
+--------------+-----------+------------+------------+------------+------------+------------+------------+
| Service Type | Person ID | Dimension1 | Dimension2 | Object1 ID | Object2 ID | dFrom | dTo |
+--------------+-----------+------------+------------+------------+------------+------------+------------+
| Service1 | 1 | wer | errerr | 54324 | AAA | 01/12/2016 | 28/02/2017 |
| Service2 | 1 | wer | errerr | 54324 | AAA | 01/12/2016 | 28/02/2017 |
| Service3 | 1 | wer | errerr | 54324 | AAA | 01/12/2016 | 28/02/2017 |
| Service1 | 2 | qerer | teety | 56767 | BBB | 01/11/2016 | 31/12/2017 |
| Service2 | 2 | qerer | teety | 56767 | BBB | 01/11/2016 | 31/12/2017 |
| Service3 | 2 | qerer | teety | 56767 | BBB | 01/11/2016 | 31/12/2017 |
| Service1 | 3 | ghgh | ryyyrtq | 34567 | BBB | 01/01/2017 | 31/12/2100 |
| Service2 | 3 | ghgh | ryyyrtq | 34567 | BBB | 01/01/2017 | 31/12/2100 |
| Service3 | 3 | ghgh | ryyyrtq | 34567 | BBB | 01/01/2017 | 31/12/2100 |
| Service1 | 4 | tyty | ty | 85432 | CCC | 01/12/2016 | 31/12/2100 |
| Service2 | 4 | tyty | ty | 85432 | CCC | 01/12/2016 | 31/12/2100 |
| Service3 | 4 | tyty | ty | 85432 | CCC | 01/12/2016 | 31/12/2100 |
| Service2 | 5 | gjhh | tytyrtt | 98988 | | 01/12/2016 | 31/12/2100 |
| Service3 | 5 | gjhh | tytyrtt | 98988 | | 01/12/2016 | 31/12/2100 |
+--------------+-----------+------------+------------+------------+------------+------------+------------+
The goal is to highlight the costs from the first table for each person in table 2, for example:
+------------+------------+------------+------------+------------------+-----------+------------+------------+
| Object1 ID | Object2 ID | tFrom | tTo | Transaction Cost | Person ID | Dimension1 | Dimension2 |
+------------+------------+------------+------------+------------------+-----------+------------+------------+
| 54324 | AAA | 01/01/2017 | 01/01/2017 | 15,45 € | 1 | wer | errerr |
| 54324 | AAA | 02/01/2017 | 02/01/2017 | 20,00 € | 1 | wer | errerr |
| 54324 | AAA | | 15/01/2017 | 3,00 € | 1 | wer | errerr |
| 54324 | AAA | 02/02/2017 | | 2,00 € | 1 | wer | errerr |
| 56767 | BBB | | 12/12/2017 | 1,78 € | 2 | qerer | teety |
| 34567 | BBB | 02/01/2017 | 02/01/2017 | 40,00 € | 3 | ghgh | ryyyrtq |
| 34567 | BBB | 04/01/2017 | 04/01/2017 | 30,00 € | 3 | ghgh | ryyyrtq |
| 34567 | BBB | | 16/01/2017 | 9,00 € | 3 | ghgh | ryyyrtq |
| 34567 | BBB | 31/01/2017 | 31/01/2017 | 45,00 € | 3 | ghgh | ryyyrtq |
| 85432 | CCC | 31/12/2016 | 31/12/2016 | 23,00 € | 4 | tyty | ty |
| 85432 | CCC | 02/01/2017 | 02/01/2017 | 12,00 € | 4 | tyty | ty |
+------------+------------+------------+------------+------------------+-----------+------------+------------+
To try and fix this on my own I googled and the most similar article I found was this one , but unfortunately it doesn't quite suit my needs. I was able to create an inner join of the above tables based on the IDs object1 and object2 and filtering, dimension table, service type and date ranges dTo> tTo and dFrom <= tTo. From this inner join I might have gotten some wrong results and I think it might have something to do with the way I defined the date range filters.
What do you guys think about this? How can I join tables like this and present the results that I showed in the third table?
Edit: I just found that there are certain transactions in my source tables identified with Object1 ID + Object2 in a specific date period that are not listed in my dimension table. Example: suppose this transaction has tTo = 02-02-2016 for Object1 ID = 34567 and object2 ID = BBBB, but my dimension table has Object1 ID = 45698 and object2 ID = BBBB dFrom = 10-02-2016 and dTo = 05 -06-2016. A sizing table is a table created manually in an excel file that I sent to a SQL Server table via SSIS and its part of the cost allocation across an internal process which is very simple (to the point that the information it contains is not always accurate as to what happened in fact, and the mapping between the transaction table and the dimension table should be approximate.I have tried for so many hours to have some idea ready to include all cases, but still with no success. I'm sure this is the main thing I'm missing, but I just don't know what.
Thanks for reading. Pls let me know if you need more information.
source to share
No one has answered this question yet
Check out similar questions: