CODEWITHSUNDEEP
sqlsql-server-2008datetimerecurrence
anna
anna

Reputation: 1011

SQL Recur every x weekday every x weeks

I'm trying to write a sql query which depending on what the user selects will recur every x day every x weeks. So the user will select that they want the job to recur on tuesdays every 2 weeks the values that are supplied are 

declare @StartDate datetime -- when the job first recurs
declare @recurrenceValue1 int -- amount of weeks
declare @recurrenceValue2 int -- day of week (mon-sun)
declare @NextOcurrance datetime -- when the job will recur

I know how to set it for the amount of weeks:

SET @NextOccurance = (Convert(char(12),@StartDate + (@RecurrenceValue1),106))

But I'm not sure how to make it roll on to the day of the week so if the @startDate is today and it should recur every 2 weeks on a tuesday it will see that 2 weeks today is wednesday so will loop until it know that the day is tuesday and that will be the @NextRecurrance date.

Thanks in advance

Upvotes: 1

Views: 5017

Answers (3)

anna
anna

Reputation: 1011

Using the principles the @DanielM set out i changed it to fit my query see below: 

    BEGIN
    SET DATEFIRST 1 --Where 1 = Monday and 7 = Sunday 
    declare @StartDate datetime -- when the job first recurs   
    declare @recurrenceValue1 int -- amount of weeks   
    declare @recurrenceValue2 int -- day of week (mon-sun)   
    declare @NextOcurrance datetime -- when the job will recur   
                        -- sets @nextoccurence to next date after x amount of weeks
 SET @NextOccurance = DATEADD(wk, @recurrenceValue1, @StartDate) -- Add on weeks /* Check if our incrementation falls on the correct day - Adjust if needed */ 
                        IF (DATEPART(dw, @NextOccurance) != @recurrenceValue2) 
                        BEGIN  
                        DECLARE @Weekday int = DATEPART(dw, @NextOccurance)      
                        SET @NextOccurance = DATEADD(dd, (@RecurrenceValue2 - @Weekday), @NextOccurance) -- Add to @NextOcurrance the number of days missing to be on the requested day of week
                        END
                    END

Upvotes: 0

DanielM
DanielM

Reputation: 969

An easy way to add a number of weeks to a date is to use (MSDN DATEADD)

DATEADD(wk, @StartDate, @recurrenceValue1)

To find out which day of the week the date you are looking at belongs, you can use (MSDN DATEPART) 

DATEPART(dw, @StartDate)

This function uses DATEFIRST to determine which day of the week is the first one (http://msdn.microsoft.com/en-us/library/ms181598.aspx) 

SET DATEFIRST 1 --Where 1 = Monday and 7 = Sunday

So for your problem (DATEFIRST being set to 1 = Monday)..

SET DATEFIRST 1

declare @StartDate datetime -- when the job first recurs
declare @recurrenceValue1 int -- amount of weeks
declare @recurrenceValue2 int -- day of week (mon-sun)
declare @NextOcurrance datetime -- when the job will recur


SET @StartDate = '2011-12-16' -- This is a Friday
SET @recurrenceValue1 = 2 -- In 2 weeks
SET @recurrenceValue2 = 2 -- On Tuesday
SET @NextOcurrance = DATEADD(wk, @recurrenceValue1, @StartDate) -- Add our 2 weeks
/* Check if our incrementation falls on the correct day - Adjust if needed */
IF (DATEPART(dw, @NextOcurrance) != @recurrenceValue2) BEGIN
    DECLARE @weekDay int = DATEPART(dw, @NextOcurrance)
    SET @NextOcurrance = DATEADD(dd, ((7 - @weekDay) + @recurrenceValue2), @NextOcurrance) -- Add to @NextOcurrance the number of days missing to be on the requested day of week
END

The logic for the number of days to add is as follow: We have 7 days in a week, how many days does it take to reach the end of this week. Add this number of days to the @recurrenceValue2 (day of week we are looking for).

PS: I can't post more than 2 HyperLinks because of my reputation. This is why the DATEFIRST URL is in plain text.

Here's some code to allow certain specific date to be handled differently. Though this code is good only for unique dates. If ones needs to skip an entire week for instance, using this code will require to add values for each day of this week to skip. For ranges other than unique days, this code should be modified to handle date ranges or specific weeks and/or day of week.

CREATE TABLE OccurenceExclusions (
    ExclusionDate DATE not null,
    NumberOfDaysToAdd int not null

    PRIMARY KEY (ExclusionDate)
)

INSERT OccurenceExclusions VALUES ('2012-01-01', 7)

SET @NextOcurrance = DATEADD(dd, COALESCE((SELECT NumberOfDaysToAdd
                           FROM OccurrenceExclusions
                           WHERE ExclusionDate = @NextOcurrance), 0), @NextOcurrance)

Upvotes: 2

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

Probably the best solution would be to use a calendar table. http://web.archive.org/web/20070611150639/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

You could then query it like so:

SELECT TOP 1 @NextOccurance = Date
FROM CalendarTable
WHERE Date >= DATEADD(week, @recurranceValue1, @StartDate)
AND DateName = @recurranceValue2

Upvotes: 0

Related Questions