Splitting Records Based on Dates

[jrdnjhnsn2]

Hey all,

I've got a bunch of policy data that has issue date and termination date (which may or may not be null). I'm trying to figure out how to split each record based on its "policy anniversary." 

For example, let's say a policy has an issue date of 10/10/2007 and termination date 12/10/2009. I want to create separate records for each of these date ranges:

1. From 10/10/2007 issue date though 12/31/2007.
2. From 1/1/2008 through 10/9/2008.
3. From 10/10/2008 through 12/31/2008.
4. From 1/1/2009 through 10/9/2009.
5. From 10/10/2009 through 12/10/2009 (the termination date).

Using DummyID 4 in my sample data as another example, my desired output is as follows:

DummyID	Policy_Start_Date	Policy_End_Date
4	1954-09-20	1954-12-31
4	1955-01-01	1955-09-19
4	1955-09-20	1955-12-31
4	1956-01-01	1956-09-19
	.
	.
	.
4	2019-09-20	2019-09-24

Does anyone know how this can be done?

[Sntrada]

Hello @jrdnjhnsn2 

You should be able to use multiple filter tools to do this. 

If you provide a sample of your data, myself or someone else in the community can create a sample workflow using the filter tool, or other tools. 

[fmvizcaino]

Hi @jrdnjhnsn2 ,

Here is a simple example where I'm using the formula to flag the policy and then using a advanced function from the output data tool to separate the data into different excel tabs.

Best,

Fernando Vizcaino

[jrdnjhnsn2]

Hi @Sntrada, I attached a sample of my data to my question. Let me know if you need anything else. Thanks!

[Sntrada]

Hi @jrdnjhnsn2 , 

I've checked out your data and re-read your question. I don't think a simple stacked filter solution would work here. Sorry! Maybe someone else can help.

[fmvizcaino]

Hi @jrdnjhnsn2 ,

I think now I've understood what you need.

What do you need to do when you don't have the termination date? To treat as today's date or simply ignore?

Best,

Fernando V.

[DavidP]

Hi @jrdnjhnsn2 

Attached workflow will work except where Issue date is the 1st January.

[jrdnjhnsn2]

Hi @fmvizcaino ,

I was literally just thinking I forgot to say what to do for the null termination date cases. I'd like to just enter today's date.

Thanks,

Jordan

[DavidP]

Here's a version that correctly deals with the case where issue date is the 1st Jan.

I made the assumption that if termination date is null to use today's date

[DavidP]

Apologies @jrdnjhnsn2, I found some bugs in how my previous workflows dealt with null termination dates.

This is now fixed. Please find updated version attached.