Need to figure out solution for grouping methodoliges. Can anyone help with this solution.

Question

Below is my input file:

EMP ID Month Day of weekIntervalDate1590087JANSUN23:301/1/20231590087JANMON0:001/2/20231590087JANMON0:301/2/20231590087JANMON1:001/2/20231590087JANMON1:301/2/20231590087JANMON2:001/2/20231590087JANMON2:301/2/20231590087JANMON3:001/2/20231590087JANMON3:301/2/20231590087JANMON4:001/2/20231590087JANTUE23:301/3/20231590087JANWED0:001/4/20231590087JANWED0:301/4/20231590087JANWED1:001/4/20231590087JANWED1:301/4/20231590087JANWED2:001/4/20231590087JANWED2:301/4/20231590087JANWED3:001/4/20231590087JANWED3:301/4/20231590087JANWED4:001/4/20231590087JANWED23:301/4/20231590087JANTHU0:001/5/20231590087JANTHU0:301/5/20231590087JANTHU1:001/5/20231590087JANTHU1:301/5/20231590087JANTHU2:001/5/20231590087JANTHU2:301/5/20231590087JANTHU3:001/5/20231590087JANTHU3:301/5/20231590087JANTHU4:001/5/2023

Above table is having information of single employeeid. In real we have multiple time interval dates and employeeid

My Expected output would be:

EMP ID Month Day of weekShiftDate1590087JANSUN23:00-4:001/1/20231590087JANTUE23:00-4:001/3/20231590087JANWED23:00-4:001/4/2023

I am not sure how to achieve prefect solution to above problem statement. Can anyone help me solve this above scenario? Your help is appreciated also would required little support in terms of solution. Please attach snip of solution.

cjaneczko · Answer

You could use two summarize tools that group the data by Employee ID and date taking the Min time on one and the Max time on another. Then Join the data together by Employee ID and Date. Then Concatenate the Min Time with the Max time. The problem I see with this though is you have some dates with one entry "23:30". How do you know that the shift is 23:00-4:00? There isn't any additional entries that show how you arrived at your expected output.