Hi, I'm hoping someone can help please. This is my first post so I hope I have given enough information. I'm using QV 11 SR1 64bit.
I have a table that defines some shift patterns. For each day there are three eight-hour shifts. The shifts are named blue, green and orange. Over a number of months the hours to which these shift colours relate change. E.g. in January orange could be 8 a.m. to 4 p.m.. but then in February orange could be midnight to 8 a.m. This is because the operators in these shifts stay with a particular shift colour but this then means they don't constantly work the same shift month after month.
DateFrom | DateTo
| TimeFrom
| TimeTo
| Shift
|
---|
01/01/12 | 31/01/12 | 00:01 | 08:00 | Blue |
01/01/12 | 31/01/12 | 08:01 | 16:00 | Orange |
01/01/12 | 31/01/12 | 16:01 | 00:00 | Green |
|
|
|
|
|
01/02/12 | 29/02/12 | 00:01 | 08:00 | Orange |
01/02/12 | 29/02/12 | 08:01 | 16:00 | Green |
01/02/12 | 29/02/12 | 16:01 | 00:00 | Blue |
etc |
|
|
|
|
The operators log transactions (e.g. stock movements in a warehouse) and the date and time of the transaction are logged in a table. I want to be able to relate the date and time of the transaction to a shift colour. I was hoping that intervalmatch might be able to help but I'm not sure how to do this when I have a date range and a time range to be able to refer to a particular shift.
Thanks.