Determining Multiple 12 Month Term periods based on Start and End Date

[pabowen]

Hello everyone,

Need some guidance on how best to approach this. I have data where I am presented with the following

Contract        ContractStartDate	ContractEndDate
A-S00027491	2020-08-06 00:00:00.000	2022-08-06 00:00:00.000
A-S00027526	2020-08-13 00:00:00.000	2022-08-13 00:00:00.000
A-S00027540	2020-08-12 00:00:00.000	2022-12-12 00:00:00.000

I must determine how many 12 month terms, or partial terms, exist for the contract and list each one out on a separate line. There may be between 1 and 5 periods. The below is the output I am looking for.

Contract        ContractStartDate	ContractEndDate
A-S00027491	2020-08-06 00:00:00.000	2021-08-05 00:00:00.000
A-S00027491	2021-08-06 00:00:00.000	2022-08-06 00:00:00.000
A-S00027526	2020-08-13 00:00:00.000	2021-08-12 00:00:00.000
A-S00027526	2021-08-13 00:00:00.000	2022-08-12 00:00:00.000
A-S00027540	2020-08-12 00:00:00.000	2020-12-11 00:00:00.000    <<<<< Note that the period shorter than 12 months is front loaded as the first term
A-S00027540	2020-12-12 00:00:00.000	2021-12-11 00:00:00.000
A-S00027540	2021-12-12 00:00:00.000	2022-12-11 00:00:00.000

Any term shorter than 12 months, or any contract with a partial term shorter than 12 months must be loaded as the first term.

I feel like this should be easy, but it is just escaping me, or is just a bit beyond me at this point.

Any help here would be greatly appreciated and would well be worth a case of beer.

Thank you greatly,
Pabowen

 

[Andrzejek]

Not to be picky, but define the "12 month term"
Looks like most (except one) records start on a date and end on the the same date next year minus one day.

So, shouldn't your A-S00027491 be:

[pre]
Contract ContractStartDate ContractEndDate
A-S00027491 2020-08-06 2021-08-05
A-S00027491 2021-08-06 2022-08-[red]05 [/red]
A-S00027491 2022-08-[red]06[/red] 2022-08-[red]06[/red] <- partial term?
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[pabowen]

Hello Andrzejek,

The terms are to be true 12 months, what you have identified is actually an artifact of the source system where it computes everything as being 1 day too long. The final product will have the enddate shortened by one day. I missed this in the example I provided.

However, still looking for the best way to break this data out and show it in the structure required.

Thanks!

 

[pabowen]

Well, I believe I have worked it out on my own. Perhaps not the best way, but it appears to work.

CREATE function [dbo].[fn_TermPeriods]()
Returns table
as
RETURN
(
	Select 1 as Number
	UNION
	Select 2 as Number
	UNION
	Select 3 as Number
	UNION
	Select 4 as Number
	UNION
	Select 5 as Number
)
GO;

WITH 
Contract_CTE as
(
	Select
	a.[Name] as [Name],
	a.SubscriptionStartDate as ContractStartDate,
	DATEADD(day, -1, a.SubscriptionEndDate) as ContractEndDate,
	CAST(DATEDIFF(month, a.subscriptionstartdate, a.subscriptionenddate) as decimal(8,2)) as ContractLength,
	CAST(DATEDIFF(month, a.subscriptionstartdate, a.subscriptionenddate) as decimal(8,2))/12 as ContractYears,
	dbo.IsInt(CAST(DATEDIFF(month, a.subscriptionstartdate, a.subscriptionenddate) as decimal(8,2))/12) isContractWholeYears,
	Floor(CAST(DATEDIFF(month, a.subscriptionstartdate, a.subscriptionenddate) as decimal(8,2))/12) ContractWholeYears,
	Ceiling(CAST(DATEDIFF(month, a.subscriptionstartdate, a.subscriptionenddate) as decimal(8,2))/12) TotalSegments,
	FROM Source.dbo.Subscription a
	
)

Select 
term.Number,
a.[Name],
CASE WHEN term.[Number] = 1 THEN a.ContractStartDate
	 WHEN a.TotalSegments >= term.Number 
			THEN DATEADD(year, (-1 * (a.TotalSegments - term.Number +1)), DATEADD(day, 1, a.ContractEndDate))
	 ELSE '' END TermStartDate,
CASE WHEN a.TotalSegments >= term.Number THEN DATEADD(year, (-1 * (a.TotalSegments - term.Number)), a.ContractEndDate)
	 ELSE '' END TermEndDate
from Contract_CTE A
CROSS APPLY fn_TermPeriods() term
WHERE a.TotalSegments >= term.number
order BY a.[Name], Number asc

Let me know if you have any critiques.