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