Checking a record is due based on the 'occuring' field

Question

I have records that have dates against them and an occurring field that contains none,yearly and monthly

id    status    note   date        last_updated   occurring
1     open      ---    01/01/2011  01/02/2010     yearly
2     open      ---    05/05/2011  03/05/2011     monthly
3     open      ---    06/06/2011  05/06/2011     none

Now I need to be able to check if a record is due (the date set has passed) which works perfect if occurring is set to none but I'm unsure of how to approach it when it's set to yearly or monthly (IE: This day in the year/month has passed)

So with the above records, if I had a method on the record called due? providing the status is 'open' it needs to return true every year when it's passed that date if it's not been updated within the year yet.

I apologise if this is confusing but it's melting my brain just trying to think of it, let alone put it into words.

Answer 1

First of all, try to find out if the platform you are using has date operations, built in or third party library.

For example, if you are using PostgreSQL, the appropriate information can be found on it's documentation page.

Hopefully, the library is good enough to provide functions like beginning of the month and end of the month, and doing time deltas. The very minimum is being able to break up the date into the parts and create new one from parts, and of course being able to compare dates. Good bonus is to be able to find previous month and next month, but these could be composed by getting beginning of the month and adding/substracting days: -1 for the previous month, +33 for the next month.

Beginning of the month can be easily constructed by setting day to 1. Beginning of the year - by setting both month and day to 1.

If I understood correctly, you need to find previous and next "due date" and then apply date comparing operations. First find due date in "this month", after that, in the previous month or next month (check the logic of the period to decide which one, or check both periods).

If your platform does not have good date library, find some open source library in the language you understand best, and try to port it to your platform.

Maybe, it will help you to understand the logic of due_date, if you can think of True condition meaning last_update belonging to one of the "due_date periods" (period has start and end dates). The trick then boils down to construct and check only nearby periods using functions/operations from your platform's datetime library.

Answer 2

First you need to be able to do date arithmetic. You've got to be able to turn a date into a number and back again. Given a number, you've got to be able to find the start and end of the month and year it falls in. You've got to be able to add 4 months to, say, October 22, 2012. This isn't too hard to do on your own (I've done it) but nowadays there's bound to be a Date class or library that will do the work for you.

Then, for a monthly occurrence, generate a date using today's month and year plus the day-of-the-month the update's due. If the resulting date is after today's date (using date arithmetic) subtract one month from it (using date arithmetic). This us the latest due date. If last_updated is before that, the next update is overdue. You can do the same thing with years, but throw the month in as well as the day.

You may need arrays to check off updates. Being early with an update might be a good thing, and you won't want to ignore one. Arrays need to be indexed by month numbers or year numbers (minus an offset for the array start date), which you'll need to convert back to the basic date number. Hopefully your Date library can handle this. Take care to always use the library: if you forget and add 3 days to a number date with a simple integer add, you could end up with a February 31st.

Hopefully this will get you started.

Answer 3

I see two big problems here.

1 - When you can't explain what the problem is clearly, then take that as a warning sign that you haven't thought about it enough yet. It is critical to your understanding of the problem that you can explain it clearly - and you should do that before you start creating fields in a database.

Another symptom of this is the field naming, where you have fields named after types (like date) it doesn't tell you anything about what that field means. That is something you should see as a warning sign when you or others around you do it. Is that field the last time the record was modified (then maybe it should be called 'modified') or the subject's birthday (in which case maybe 'dob' or 'birthday'). Giving it useful name will help you or whoever has to deal with the code you've created.

2 - Solving the actual problem... from what I can gather for example you have some kind of reminder/billing system which operates over different periods for customers with an open account. So it's best to consider that problem in isolation for now, and for our purposes id, status, note are not fields which are relevant to this problem.

You need 2 fields to get to the solution:

due - date, nullable - when the next reminder/action is due

recurs - date/timespan, nullable - the timespan of the next time to process this record.

To find out what records are due for an update you query against the current date.

When you process the record you use the database or software's date/time system to if it's not null then you add the recurs to due, write back your next due date and anything you don't need up update again make null.