Hello, hope someone can help with this please.
I have a set of transactions which have a date and time and also a department in a table. e.g.
TransKey | Date | Time | Dept |
100001 | 01/10/19 | 07:00 | 100 |
100002 | 01/10/19 | 14:50 | 100 |
100003 | 01/10/19 | 08:00 | 200 |
100004 | 01/10/19 | 13:05 | 200 |
I also have a shift pattern in another table which details day by day for each department a number of shifts, e.g. in this example dept 100 has two shifts and dept 200 has 3 shifts. I've only shown one date but the real table has many.
Dept | StartDate | EndDate | StartTime | EndTime | Shift |
100 | 01/10/19 | 01/10/19 | 06:00 | 12:00 | A |
100 | 01/10/19 | 01/10/19 | 12:01 | 18:00 | B |
200 | 01/10/19 | 01/10/19 | 06:00 | 10:00 | A |
200 | 01/10/19 | 01/10/19 | 10:01 | 14:00 | B |
200 | 01/10/19 | 01/10/19 | 14:01 | 18:00 | C |
I would like to join the 2 tables (via intervalmatch??) so that each of the transactions is assigned a shift by reference to the shift pattern table. So, the transactions would have the following shifts based on the above
100001 shift A,
100002 shift B,
100003 shift A,
100004 shift B.
I'm not sure how to define the interval match so that the dates, times and departments are taken into account.
Thanks.
