SQL Server Date Logic - Finding the next reminder date

Given the following database table:

StartDate DATETIME  -- day that the reminder period starts
LastReminderDate DATETIME -- the last time the reminder triggered
DayOfMonth INT -- the day of the month to remind the user
Interval INT   -- how often in months to remind the user

How can I figure out the next reminder date based on these values? For example:

StartDate = '6/1/2011'
LastReminderDate = '6/5/2011'
DayOfMonth = 5 -- remind on the 5th of the month
Interval = 2 -- remind every other month

For this particular example, the next reminder date should be 8/5/2011 because it reminds on the 5th of the month every two months. How would I write a function to figure this out?

If LastReminderDate is NULL, then LastReminderDate should be equal to StartDate

UPDATE:

StartDate = '6/1/2011'
LastReminderDate = NULL
DayOfMonth = 5
Interval = 2

In this case, there was no last reminder date. The first time the reminder would occur would be 6/5/2011. The solutions below seem to be returning 8/5 in this case.

Here are some specific rules:

  • The Reminder should always occur on whatever DayOfMonth is. If DayOfMonth would be illegal for the given month then it should be the last day of that month. For example....if DayOfMonth is 31 and the next reminder date would fall on June 31, then it should be June 30th instead.
  • The next reminder date should always be based off of the Last Reminder Date plus the Interval. If Last Reminder Date does not match the Day of Month, then it could potentially be more than what the interval was. For example...if Last Reminder was 6/1/2011 and the interval is 2 months, but the reminder is for the 20th of the month, then the next reminder will be 8/20/2011.
  • If there is no last reminder date, then use the Start Date instead of last reminder date...but this will use the earliest date in the future. If start date was 6/1/2011 and day of month is 5, then this will be 7/5/2011 since today is 6/22/2011. If Day of Month was 25 then it would be 6/25/2011

Answers


DECLARE
@StartDate AS datetime,  -- day that the reminder period starts
@LastReminderDate AS datetime, -- the last time the reminder triggered
@DayOfMonth AS integer, -- the day of the month to remind the user
@Interval AS integer   -- how often in months to remind the user

SET @StartDate = '6/1/2011'
SET @LastReminderDate = '6/5/2011'
SET @DayOfMonth = 5 -- remind on the 5th of the month
SET @Interval = 2 -- remind every other month

SELECT
CASE
   WHEN @LastReminderDate IS NULL
   THEN
     CASE WHEN Day(@StartDate) <= @DayOfMonth
       THEN DateAdd( month, ((Year( @StartDate ) - 1900) * 12) + Month( @StartDate ) - 1, @DayOfMonth - 1 )
       ELSE DateAdd( month, ((Year( @StartDate ) - 1900) * 12) + Month( @StartDate ) - 0, @DayOfMonth - 1 )
     END
  ELSE DateAdd( month, @Interval, @LastReminderDate )
END

The meat of this is the last four lines, the SELECT CASE ... END statement. I provided a whole script that lets you plug in different values, and see how the SELECT CASE ... END behaves for those test values.

But to just use this on your table, use only the last four lines (and remove the @ from the front of the names so they match the table's column names).

You could also generalize this so that Interval doesn't have to be months. If your table had an IntervalType column, you could supply that as the first argument to DateAdd(). See the docs but some common intervals are days, months, years, and so on.

EDIT2: Respect DayOfMonth.


Since you want to use the StartDate if there is no LastReminderDate, then you'll want to use COALESCE for that bit of logic: COALESCE(LastReminderDate, StartDate)

Now, get to the last of the previous month: DATEADD(DAY, -DAY(COALESCE(LastReminderDate, StartDate)), COALESCE(LastReminderDate, StartDate))

Finally, add the months and then get to the date that we need:

DATEADD(MONTH, Interval, DATEADD(DAY, -DAY(COALESCE(LastReminderDate, StartDate)) + DayOfMonth, COALESCE(LastReminderDate, StartDate)))

This will potentially go forward less than two months if the date of the last reminder was on a day of the month after the "DayOfMonth" that's configured for the reminder. You should be able to tweak that depending on what your business logic is in that situation.


Need Your Help

Sharepoint FullTextSQLQuery index will not clear

c# .net sharepoint

After resetting the crawled content from my shared service I can still see all of the indexed data by using a FullTextSQLQuery. If I try searching using the OOB search on my site, I see no results...

Quick Search out of bounds of array and confusing array behaviour

c# quicksort parallel-arrays

I'm having multiple problems with my program (C#.NET) and have no idea what's causing them.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.