views:

119

answers:

2

Repetitive Dates:

Billing cycles come in a lot of different formats, for example: "the first of the month", "third Friday of the month", or "first weekday on or after 21st day after the 13th of the month" (thanks visa!). My goal is to be able to represent these different billing cycles in one easily parsed database text field.

+1  A: 

You forgot weekend processing. If my bill is due on a Sunday, I want it to show up on Friday. That kind of thing. I think there are three possibilities:

  1. Always before the weekend
  2. Always after the weekend
  3. If it's Saturday, Friday, and if it's Sunday, Monday
Jim Barrows
hmm. good point. maybe add another letter to DOW: "p" meaning previous buisness day?
alumb
B for before weekend. A for after weekend. S for split?
Jim Barrows
s is taken for saturday.
alumb
A: 

Current solution:

YYYY/MM/DD+YY/MM/DD+DOW
  • all blocks (delimited by +) are optional as well as all parts of each block.
  • first block denotes the starting date
    • if blank - denotes today
    • if missing portion - denotes all... ie "//12" would denote the 12th of the month every month.
    • negative denotes counting from the end of the period.. ie "//-1" denotes the last day of the month
    • overruns are carried into the next month ie "//40" would be 40 days after the first of the month
  • second block denotes the change in date between repetitions
    • if blank - set date (from first block)
    • Add present portions - ie "/1/" means every month after the given starting date
  • third block denotes the day of the week the calculated date should be moved to
    • available are [m,t,w,r,f,s,d,b] (7 days of the week, "business day")

Calculation:

  • Calculation start on the date specified in the first block, filling in the missing portions with the date the record is added.
  • the DOW is then added to get a valid starting date. This is the first date of the event.
  • the next date is calculated by adding the second block to the last calculated first block and adding the DOW to get the next date.

Examples:

  • on march 12th, 2008 - "2008/3/12"
  • the first of the month every month - "//1+/1/"
  • third Friday of the month every month - "//21+/1/+f"
  • first weekday on or after 21st day after the 13th of the month - "//34+/1/+b"
  • every 2 weeks from today - "+//14"
  • last day of every second month starting with march - "/3/-1+/2/"
alumb