Project Description:
We need an MSSQL function to be built: GetNextOccurence(onOrAfterDate, startDate, endDate, endAfterTimes, frequencyInt, repeatEveryTimes, frequencyDetailInt)
It will return a date which is the next occurrence (inclusive of 'onOrAfterDate').
If FrequencyInt=4 and FrequencyDetailInt = 8 (this is a monthly schedule repeating on the day of the month). If the start date does not occur in the month (31st) skip until the next month that includes the start date.
Arguments
-"onOrAfterDate" the inclusive first date to look for the next occurrence
-"startDate" the StartDate of the Repeat being passed. See table 'Repeats'
-"endDate" the EndDate of the Repeat being passed. See table 'Repeats'
-"endAfterTimes" the number of occurrances to have for the Repeat being passed. See table 'Repeats'
-"frequencyInt" the type of Repeat it is (eg once, daily, weekly, etc.). See table 'Repeats'
-"repeatEveryTimes" is how often to repeat the specific schedule. See table 'Repeats'
-"frequencyDetailInt" only set for weekly and monthly schedules. it defines which days of the week or what day of the month respectively. See table 'Repeats'
Requirements:
-Make it as fast as possible. No loop should iterate more than 14 times
-- Creating table 'Repeats'
CREATE TABLE [dbo].[Repeats] (
[Id] uniqueidentifier NOT NULL,
[StartDate] datetime NOT NULL, -- The first date of the schedule. The schedule will always begin on the start date regardless of what the repeat schedule is
[EndDate] datetime NULL, -- The last day this repeats. If this and EndAfterTimes is NULL the repeat has no end.
[EndAfterTimes] int NULL, -- The number of times to repeat from the StartDate. If this and EndDate is NULL the repeat has no end.
[FrequencyInt] int NOT NULL, -- See below
[RepeatEveryTimes] int NOT NULL, -- How often to repeat (eg '1' would mean that you repeat every day/month/year depending on the FrequencyInt)
[FrequencyDetailInt] int NULL --See below
);
GO
FrequencyInt will be an integer between 1-5. Combined with RepeatEveryTimes, it determines how often to repeat.
1 = Once. Only occur once on the start date
2 = Daily. Occur every day, every 2 days, every 10th day...
3 = Weekly. Occur every week, every 2 weeks, every 5th week...
4 = Monthly. Occur every month, every 2 months, every 6th month...
5 = Yearly. Occur every year, every 3 years, every 10 years...
FrequencyDetailInt is only set for Weekly and Monthly schedules
Weekly FrequencyDetailInt is used to define which days of the week to occur on
It combines the days of the week into an integer
12 = Occur on Sunday and Monday
137 = Occur on Sunday, Tuesday and Saturday
123456 = Occur on Sunday, Monday, Tuesday, Wednesday, Thursday and Friday
Monthly FrequencyDetailInt will be an integer between 8-14. It is set as follows
8 = OnDayInMonth. --Ex. The 30th of the month.
10 = LastOfMonth --Ex. Febuary 28th
11 = FirstOfDayOfWeekInMonth --Ex. First Monday
12 = SecondOfDayOfWeekInMonth --Ex. Second Monday
13 = ThirdOfDayOfWeekInMonth --Ex. Third Monday
14 = LastOfDayOfWeekInMonth --Ex. Last Monday
