Hi All,
I am facing issue in interval match for having multiple dates in fact table.
I have one table in which start date and end date and in second table there are two dates which i have to identify in which range they comes. But interval match as create synthetic key for single date and for more than one date even it is creating loop and synthetic key.
Attached the sample data for reference with output.
Tab1:
| PID | Phase | Start_Date | End_Date |
| A1 | Q1 | 4/1/2016 | 4/7/2016 |
| A1 | Q1 | 4/8/2016 | 4/15/2016 |
| A1 | Q1 | 4/16/2016 | 4/22/2016 |
| A1 | Q1 | 4/23/2016 | 4/30/2016 |
| A1 | Q2 | 5/1/2016 | 5/14/2016 |
| A1 | Q2 | 5/15/2016 | 5/21/2016 |
| A1 | Q2 | 5/22/2016 | 5/28/2016 |
| A1 | Q2 | 5/29/2016 | 6/4/2016 |
Tab2:
| PID | Phase | ActualDate | PlannedDate | PartID |
| A1 | Q1 | 4/4/2016 | 4/5/2016 | P1 |
| A1 | Q1 | 4/6/2016 | 4/7/2016 | P2 |
| A1 | Q1 | 4/11/2016 | 4/12/2016 | P3 |
| A1 | Q1 | 5/2/2016 | 5/5/2016 | P4 |
| A1 | Q2 | 5/16/2016 | 5/16/2016 | P2 |
| A1 | Q2 | 5/18/2016 | 5/23/2016 | P3 |
| A1 | Q2 | 5/19/2016 | 5/19/2016 | P1 |
| A1 | Q2 | 5/27/2016 | 5/19/2016 | P2 |
Output:
| PID | Phase | Start_Date | Count(PID) by ActualDate | Count(PID) by PlannedDate |
| A1 | Q1 | 4/1/2016 | 2 | 2 |
| A1 | Q1 | 4/8/2016 | 1 | 1 |
| A1 | Q1 | 4/16/2016 | 0 | 0 |
| A1 | Q1 | 4/23/2016 | 0 | 0 |
| A1 | Q2 | 5/1/2016 | 0 | 0 |
| A1 | Q2 | 5/15/2016 | 3 | 2 |
| A1 | Q2 | 5/22/2016 | 1 | 1 |
| A1 | Q2 | 5/29/2016 | 0 | 0 |
Thanks in advance.
Sumita Chawla
