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>