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?

[jrdnjhnsn2]

Thanks, @DavidP! That's a thing of beauty. Works perfectly.

[DavidP · Accepted solution]

Sorry @jrdnjhnsn2 this is getting very embarrassing, I keep messing up the multi-row formula to accommodate all the different angles and in trying to fix one thing, I break another.

As a result, the previous version was wrong too. The attached workflow is the final version where everything works they way it should.

The multi-row formula should be:

IF

IsNull([Row-1:Period Start]) OR
tostring(DateTimeYear([Period Start]))+'-01-01'!=[Period Start] OR
tostring(DateTimeYear([Issue date]))+'-01-01'=[Issue date]

THEN

tostring(DateTimeYear([Period Start]))+'-12-31'

ELSE

DateTimeAdd(DateTimeAdd([Row-1:Period Start],1,'year'),-1,'day')

ENDIF