title: SQL - Function to get next due date (or past due date)
body: <p>I have read several articles on dates and schedules in SQL, but the solution to my problem continues to elude me.</p>
  
  <p>I'm using SQL Server 2008 r2.</p>
  
  <p>I need to be able to provide the user with a next due date (even if it's in the past). The user is able to create a schedule for importing some data by specifying the frequency (daily, weekly, monthly), a "multiplier" (i.e. every 3 days, every 2 weeks,  every 6 months, etc...) and a start date.</p>
  
  <p>So, my data looks something like this:</p>
  
  <pre><code>Frequency | Multiplier | Start date
  Weekly    | 2          | 02/15/2012
  Monthly   | 1          | 03/01/2012
  </code></pre>
  
  <p>The first record would be scheduling an import every 2 weeks starting on 2/15/2012
  The second record would be every month on the 1st (could be a problem if they pick the 31st, haven't addressed that yet).</p>
  
  <p>I also have the last import date, which may or may not have been on time.
  So, I want to write a function where I can pass in the frequency (weekly, daily, etc...), the multiplier, and the last import date, and calculate when the next "due" date is after the last import date. </p>
  
  <p>So, given record 1 above (Import every 2 weeks starting on 2/15/2012),  If I have a last import date of 3/1/2012, I would need to return 3/14/2012. Obviously, given that today is the 22nd, this date would be past due and I could mark it as such in any UI that uses this data.  If my last import date is 3/22/2012, I would return 3/28/2012 as the next due date, and so on.
  I will probably need to break out the different frequencies (day, week, month) to three functions in order to simplify the logic.</p>
  
  <p>I've looked into a Calendar table <a href="http://web.archive.org/web/20070611150639/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html" rel="nofollow noreferrer">as described here.</a></p>
  
  <p>However, if the solution is there, I'm not sure what it is exactly. I feel very close to a solution, but just haven't yet got it. </p>
  
  <p>Any help is much appreciated.</p>
  
  <p>Thanks,
  Tony</p>