Hi, I provided below "Have" and "Want" data. "Have" is what I have and "Want" is what I want as the end result.
Basically I have data for each ID on a date have shift times and different types of breaks. For example: If an agent works 8:00AM to 5:00PM and took lunch break from 12:00 to 1:00, my result data should show Actual time he worked, like, 8:00 to 11:59 and 1:01 to 5:00 PM.
Have Date ID CODE Start_time Stop_time 15-Feb 2151 Shift 15FEB2016:08:00:00 15FEB2016:17:00:00 15-Feb 2151 Break1 15FEB2016:10:30:00 15FEB2016:10:45:00 15-Feb 2151 Lunch 15FEB2016:12:00:00 15FEB2016:13:00:00 15-Feb 2151 Break2 15FEB2016:15:15:00 15FEB2016:15:30:00 15-Feb 3000 Shift 15FEB2016:09:00:00 15FEB2016:18:00:00 15-Feb 3000 Break1 15FEB2016:10:30:00 15FEB2016:10:45:00 15-Feb 3000 Lunch 15FEB2016:12:00:00 15FEB2016:13:00:00 15-Feb 3000 Break2 15FEB2016:15:15:00 15FEB2016:15:30:00 Want Date ID CODE Start_time Stop_time Shift_Begin Shift_End 15-Feb 2151 Shift 15FEB2016:08:00:00 15FEB2016:10:29:29 15FEB2016:08:00:00 15FEB2016:17:00:00 15-Feb 2151 Shift 15FEB2016:10:45:00 15FEB2016:11:59:59 15FEB2016:08:00:00 15FEB2016:17:00:00 15-Feb 2151 Shift 15FEB2016:13:00:00 15FEB2016:15:14:59 15FEB2016:08:00:00 15FEB2016:17:00:00 15-Feb 2151 Shift 15FEB2016:15:30:00 15FEB2016:17:00:00 15FEB2016:08:00:00 15FEB2016:17:00:00 15-Feb 3000 Shift 15FEB2016:09:00:00 15FEB2016:10:29:29 15FEB2016:09:00:00 15FEB2016:18:00:00 15-Feb 3000 Shift 15FEB2016:10:45:00 15FEB2016:11:59:59 15FEB2016:09:00:00 15FEB2016:18:00:00 15-Feb 3000 Shift 15FEB2016:13:00:00 15FEB2016:15:14:59 15FEB2016:09:00:00 15FEB2016:18:00:00 15-Feb 3000 Shift 15FEB2016:15:30:00 15FEB2016:18:00:00 15FEB2016:09:00:00 15FEB2016:18:00:00
... View more