0

My app requires users to schedule recurring events that can recur daily, weekly, monthly, or bi-weekly.

By bi-weekly, I mean every fortnight (14 days) starting from an arbitrary date value provided at the time of creation.

My jobs table has two columns to support this: job_frequency_id and job_frequency_value. I'm able to schedule all types except for bi-weekly.

The first col is an FK to the job_frequencies table; it contains daily, weekly, monthy, bi-weekly values. The job_frequency_value contains the value corresponding to the frequency.

For example: If a job has a job_frquency_id == 3 and job_frequency_value == 10, it will run every 10th day of the month.

How do I add bi-weekly support without tampering with my db structure? I will use the job_frequency_value col to store the start date of the 14 day period, but I'm unsure of the calculation going forward.

Mohamad
  • 34,731
  • 32
  • 140
  • 219
  • Which days are you considering the first and last of the week? How would you prefer to handle months that start in the middle of a week? In that case, would the first "week" be only the remainder until the last day of the week (so, if the month started on a Thursday, and Saturday was the last day of the week, then the first week would be considered the days from Thursday-Saturday)? Or, would you just want to consider the first day of the month to be the beginning of the first week, and every subsequent week be just the next 7 day interval? – Jake Feasel Dec 08 '11 at 18:33
  • @Jake, as I mentioned below, I think I've made a hash of my question. Why I really need is a way of doing something bye-weekly without tampering with the db structure. It's easy enough, using my existing columns, to schedule things daily, monthly, and weekly. I can't figure out how to do it for bye-weekly. – Mohamad Dec 08 '11 at 18:37
  • @Jake, I've updated my question accordingly. – Mohamad Dec 08 '11 at 18:42
  • Not to be a pedant, but I think you want to call it "bi-weekly", not "bye-weekly". – Jake Feasel Dec 08 '11 at 18:49
  • 1
    @Mohamad To be even more annoying bi-weekly is valid for both every other week and twice a week. Which you mean is an important distinction. – Sign Dec 08 '11 at 18:52
  • 1
    @Mohamad to add yet further annoyance - biweekly is usually taken to mean every two weeks, regardless of how they fall in a given month. Based on your original version of your question, it sounded more like you were asking about "semi-monthly", or scheduling things to occur twice in a given month (a much trickier proposition due to the irregularity of months). Please let us know which you are attempting. – Jake Feasel Dec 08 '11 at 19:02
  • @Jake, by bi-weekly, I meant every 14 days from an arbitrary date provided at the time of creation. This gets more and more complex the more I think about it. – Mohamad Dec 08 '11 at 19:14
  • Yeah, I'm not getting it. Where does ColdFusion come in to play here? Are you trying to use the ColdFusion Scheduler? Are you trying to figure out how to add 14 days to a date? How about showing what you have as values for the other frequency types? – ale Dec 08 '11 at 19:42
  • @Mohamad - Why avoid modifying the table? I do not think two columns is sufficient for a scheduling app. Unless `jobs` is just a template containing the available options, and "scheduled" jobs are stored elsewhere. Otherwise, I think you may be trying to fit a square peg into a round hole. – Leigh Dec 08 '11 at 19:47

2 Answers2

2

Say your starting date is stored as a variable named 'createdDate'.

nextFortnight = DateAdd("ww", job_frequency_value*2, createdDate);
Jake Feasel
  • 16,785
  • 5
  • 53
  • 66
  • BTW - I've left this terse since I can't really tell the context of your request. If you explain a bit as to how you need to use this frequency logic, I could probably extend the example to make it more useful. – Jake Feasel Dec 09 '11 at 01:00
0

can you wrap your scheduled task in a and set it to run every week?

Something like

<cfif DateDiff('ww',CreateDate(2011,01,01),Today'sDate) MOD 2 EQ 1>

That way if the weeks are odd your scheduled task runs completely and if it's an odd week then it runs the scheduled task, but ignore all your code.

Matt Busche
  • 14,216
  • 5
  • 36
  • 61