BP Activity - Query for next date

[msantaana]

Hi Experts,

Will you be able to help me come up with a SQL query that will compute for the next date based on the Recurrence, Interval and Day of week in the Activity (OCLG).

Here's an example:

The Activity is Set to Weekly with an Interval of 1 and the Day of Week is Thursday. The screen on the right is a sample of the list of next dates for the next 6 months. I need a query that will calculate for this that is dependent on the Recurrence (Daily, Weekly, Monthly, Annually) and the interval that can be saved on a #Temp Table. Is this something possible? Thanks in advance

Maria

[dinesh-pn]

Dear Maria,

Try this Query;

DECLARE @date DATETIME, @Weaks INT

SET @date = '2018-11-15 00:00:00'

SET @Weaks = 5;

WITH TEST AS (SELECT 0 AS DAY UNION ALL SELECT DAY + 1 FROM TEST WHERE DAY < @Weaks -1 )

SELECT DATEADD(WK,DAY,@date) FROM TEST