Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have an interesting problem that I am struggling with resolving

Multi row dataset that needs to be combined to produce a start and end times.

Below is sample data with expected results. Any help will be greatly appreciated

Raw Data
Plan ID		OTHER_TIME			TEAM_NO	NAME	INSTANT
30686834	4/23/2021 16:00		159		Add		4/20/21 6:49 AM
30686834	4/23/2021 16:00		159		Add		4/20/21 2:03 PM
30686834	4/23/2021 16:00		159		Add		4/20/21 2:03 PM
30686834	4/23/2021 16:00		160		Add		/20/21 5:15 PM
30686834	4/23/2021 16:00		160		Remove	4/21/21 7:33 AM
30686834	4/23/2021 16:00		160		Add		4/21/21 7:34 AM
30686834	4/23/2021 16:00		160		Remove	4/21/21 7:34 AM
30778380	5/15/2022 5:22		16		Add		5/10/22 5:22 AM
30778380	5/15/2022 5:22		16		Add		5/10/22 5:23 AM
30698999	7/30/2021 13:35		41		Add		7/18/21 8:29 AM
30698999	7/30/2021 13:35		41		Add		7/19/21 9:18 AM
30698999	7/30/2021 13:35		41		Remove	7/22/21 8:40 AM
30698999	7/30/2021 13:35		158		Add		6/5/21 11:59 AM
30698999	7/30/2021 13:35		158		Remove	6/5/21 12:28 PM
30698999	7/30/2021 13:35		159		Add		6/11/21 7:31 PM
30698999	7/30/2021 13:35		162		Add		6/5/21 12:54 PM
30698999	7/30/2021 13:35		162		Remove	6/11/21 7:31 PM



Rules:
Add Instant = Start Time
Remove Instant = End Time

Use first "Add" instant as start if multiple Add is listed for a plan id and team number. Ignore the susequent Adds
If no remove is listed for a plan id and team number then use the first Add instant as start time and Other Time as end time
A plan id and team number can have multiple adds and multiple removes

Expected Results:
Plan ID 	TEAM_NO 	START_TIME 				END_TIME
30686834 	159 		4/20/21 6:49 AM 	4/23/21 4:00 PM
30686834	160 		4/20/21 5:15 PM 	4/21/21 7:33 AM
30686834 	160 		4/21/21 7:34 AM 	4/21/21 7:34 AM
30778380 	16 			5/10/22 5:22 AM 	5/15/2022 5:22
30698999 	41 			7/18/21 8:29 AM 	7/22/21 8:40 AM
30698999 	158 		6/5/21 11:59 AM 	6/5/21 12:28 PM
30698999 	159 		6/11/21 7:31 PM 	7/30/2021 13:35
30698999 	162 		6/5/21 12:54 PM 	6/11/21 7:31 PM


What I have tried:

Tried multiple partitioning with Add and Remove but I just can't get the desired results. Also tried lead/lag but given there can be n number of Add and n number of Remove it is not feasible to use these functions
Posted
Updated 5-Aug-22 15:02pm
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900