Split the table based on multiple conditions

[tww]

Hi all,

Below is the original dataset that I want to split it into 3 different datasets as pass, open, and exception. Could someone kindly show me how to go about doing this? Any help is greatly appreciated. Thank you.

1. Pass table will contain all the ones with current status = success and the difference between current successful finished date and first started date is equal or less than 1 day for the same client. I also want to remove client where there was no failure. i.e. no further work on those ones.

2. Open table will contain all the ones with status=failed and no success thereafter for the same client.

3. Exception table will contain all the ones with mix of failed and success status, but the finished date of last success is more than 1 day from the started date of first failure.

See the example output below.

Original dataset:

Client	Status	Started	Finished
adfs02.ccnb.com	failed	2/27/2022	2/27/2022
adfs02.ccnb.com	success	2/28/2022	2/28/2022
devcsssql01.ccnb.com	failed	2/27/2022	2/27/2022
devcsssql01.ccnb.com	success	2/28/2022	2/28/2022
devdbasql01.ccnb.com	failed	2/14/2022	2/14/2022
devdbasql01.ccnb.com	failed	2/15/2022	2/15/2022
devdbasql01.ccnb.com	failed	2/15/2022	2/15/2022
devdbasql01.ccnb.com	failed	2/15/2022	2/15/2022
devdlakesql01.ccnb.com	failed	2/8/2022	2/9/2022
devdlakesql01.ccnb.com	success	2/9/2022	2/9/2022
devdlakesql01.ccnb.com	failed	2/9/2022	2/9/2022
devdlakesql01.ccnb.com	failed	2/10/2022	2/10/2022
devdlakesql01.ccnb.com	success	2/10/2022	2/10/2022
devdlakesql01.ccnb.com	failed	2/12/2022	2/13/2022
devdlakesql01.ccnb.com	failed	2/13/2022	2/13/2022
devdlakesql01.ccnb.com	failed	2/13/2022	2/14/2022
devdlakesql01.ccnb.com	failed	2/14/2022	2/14/2022
devdlakesql01.ccnb.com	success	2/14/2022	2/15/2022
devdlakesql01.ccnb.com	failed	2/28/2022	2/28/2022
devspdb01.ccnb.com	failed	2/28/2022	3/1/2022
prddlakesql01.ccnb.com	failed	2/28/2022	2/28/2022

Pass:

Client	Status	Started	Finished
adfs02.ccnb.com	failed	2/27/2022	2/27/2022
adfs02.ccnb.com	success	2/28/2022	2/28/2022
devcsssql01.ccnb.com	failed	2/27/2022	2/27/2022
devcsssql01.ccnb.com	success	2/28/2022	2/28/2022
devdlakesql01.ccnb.com	failed	2/8/2022	2/9/2022
devdlakesql01.ccnb.com	success	2/9/2022	2/9/2022
devdlakesql01.ccnb.com	failed	2/9/2022	2/9/2022
devdlakesql01.ccnb.com	failed	2/10/2022	2/10/2022
devdlakesql01.ccnb.com	success	2/10/2022	2/10/2022

Open:

Client	Status	Started	Finished
devdbasql01.ccnb.com	failed	2/14/2022	2/14/2022
devdbasql01.ccnb.com	failed	2/15/2022	2/15/2022
devdbasql01.ccnb.com	failed	2/15/2022	2/15/2022
devdbasql01.ccnb.com	failed	2/15/2022	2/15/2022
devdlakesql01.ccnb.com	failed	2/28/2022	2/28/2022
devspdb01.ccnb.com	failed	2/28/2022	3/1/2022
prddlakesql01.ccnb.com	failed	2/28/2022	2/28/2022

Exception:

Client	Status	Started	Finished
devdlakesql01.ccnb.com	failed	2/12/2022	2/13/2022
devdlakesql01.ccnb.com	failed	2/13/2022	2/13/2022
devdlakesql01.ccnb.com	failed	2/13/2022	2/14/2022
devdlakesql01.ccnb.com	failed	2/14/2022	2/14/2022
devdlakesql01.ccnb.com	success	2/14/2022	2/15/2022

[Clifford_Coon · Accepted solution]

Hi tww,

Here is something that gets there if a little complex. (I incremented first fail date manually to a value of 2)

 

[tww]

Hi @Clifford_Coon 

Thank you so much for helping out. As a new user, I would have no chance of developing such complicated workflows. Now I need to go through them in details to understand the logics. :)

Thank you