Aniversary from a dynamic Frequency

I needed to find the next occurrence from a start date on an incremental frequency ( Monthly, Quarterly, Biannual, Annually. ) Large volumes of data and performance implications meant that a round trip to .NET to loop through the data and process were not acceptable in this case.

</pre>
<pre>with frequencies as
 (
 SELECT 'M' Frequency, 1.0 multiplier
 UNION ALL
 SELECT 'Q' Frequency, 3.0 multiplier
 UNION ALL
 SELECT 'S' Frequency, 6.0 multiplier
 UNION ALL
 SELECT 'A' Frequency, 12.0 multiplier
 )

select top 10 Frequency,
 StartDate,
 DateAdd(m, CEILING(DATEDIFF(m, StartDate, getdate()) / multiplier) * multiplier, StartDate ) NextOccurance
 from
 ADB..ATable
 INNER JOIN frequencies FRQ
 on ADB..ATable.Frequency = FRQ.Frequency
 where StartDate is not null
<pre>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s