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

[kapilthakur]

Below is my input file:

EMP ID	Month	Day of week	Interval	Date
1590087	JAN	SUN	23:30	1/1/2023
1590087	JAN	MON	0:00	1/2/2023
1590087	JAN	MON	0:30	1/2/2023
1590087	JAN	MON	1:00	1/2/2023
1590087	JAN	MON	1:30	1/2/2023
1590087	JAN	MON	2:00	1/2/2023
1590087	JAN	MON	2:30	1/2/2023
1590087	JAN	MON	3:00	1/2/2023
1590087	JAN	MON	3:30	1/2/2023
1590087	JAN	MON	4:00	1/2/2023
1590087	JAN	TUE	23:30	1/3/2023
1590087	JAN	WED	0:00	1/4/2023
1590087	JAN	WED	0:30	1/4/2023
1590087	JAN	WED	1:00	1/4/2023
1590087	JAN	WED	1:30	1/4/2023
1590087	JAN	WED	2:00	1/4/2023
1590087	JAN	WED	2:30	1/4/2023
1590087	JAN	WED	3:00	1/4/2023
1590087	JAN	WED	3:30	1/4/2023
1590087	JAN	WED	4:00	1/4/2023
1590087	JAN	WED	23:30	1/4/2023
1590087	JAN	THU	0:00	1/5/2023
1590087	JAN	THU	0:30	1/5/2023
1590087	JAN	THU	1:00	1/5/2023
1590087	JAN	THU	1:30	1/5/2023
1590087	JAN	THU	2:00	1/5/2023
1590087	JAN	THU	2:30	1/5/2023
1590087	JAN	THU	3:00	1/5/2023
1590087	JAN	THU	3:30	1/5/2023
1590087	JAN	THU	4:00	1/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 week	Shift	Date
1590087	JAN	SUN	23:00-4:00	1/1/2023
1590087	JAN	TUE	23:00-4:00	1/3/2023
1590087	JAN	WED	23:00-4:00	1/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]

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.