[Resolved] mysql decreasing time frame notification query problem

emrys404

Okay this is a bit of a logic problem and my brain wont let me work it out right now 🙂

Okay i have a database with a bunch of records with varying dates from six months to approx 5 years. I want to send a few notices to all the records approaching 5 years old (one at six months till 5years old, one at 3 months, 2 months, 1 month and then 1 when it is over 5 years old). Because i only want to send them one notice per time period, i gave their db record a counter which updates every time a notice is sent out.
I'm basing my query on if the record is between the two dates (ie. between six and 3 months till 5 years old) and if it has the counter less than what it should be if the notice for that time frame has been sent out.

The problem i am having with this method is if no notices are sent out due to admin lazyness or vacation or whatever and a few of the records slip into the next time period they arent going to be picked up by the query. ie. a record has 3 months and 1 day until it reaches 5 years old, and the counter says it hasnt gotten its 3 month notification yet. In two days it will slip into the 2 months till 5 years category but now it will dissapear from the query because its counter hasnt updated.

here is the query to select all records that need a notification sent

    $result = mysql_query("SELECT * FROM main WHERE (filedate < '".date("Y-m-d",strtotime("-4 years -6 months"))."' and filedate > '".date("Y-m-d",strtotime("-4 years -9 months"))."' and reminder = '0') or (filedate < '".date("Y-m-d",strtotime("-4 years -9 months"))."' and filedate > '".date("Y-m-d",strtotime("-4 years -10 months"))."' and reminder = '1') or (filedate < '".date("Y-m-d",strtotime("-4 years -10 months"))."' and filedate > '".date("Y-m-d",strtotime("-4 years -11 months"))."' and reminder = '2') or (filedate < '".date("Y-m-d",strtotime("-4 years -11 months"))."' and filedate > '".date("Y-m-d",strtotime("-5 years"))."' and reminder = '3') or (filedate <= '".date("Y-m-d",strtotime("-5 years"))."' and reminder = '3') LIMIT 0, $limit", $link) or die (mysql_error());

Thanks in advance,
emrys

emrys404

Heh, i know its a bit of a brain teaser. Let me know if i can make anything clearer.

Thanks in advance
-emrys

emrys404

Okay i am still having problems with this... Is there anyway i can modify the problem to fit a solution? 🙂 any ideas? Basically all i'm trying to do is keep track of records that reside with in a date range and if they generated a notification, mark them so they dont appear until they enter the next date range all while making sure they dont dissapear if they fall into the next date range without having a notification generated.....

help help help

thanks in advance
-emrys

Roger_Ramjet

Several ways to crack this nut.

track the notifications explicitly.
Create a table that records the actual notices sent, and then count them before you send the next one. If you also record the type of each notification then you can add some logic to determine when the next one is due and what it should be.

2 create 'levels'
Define a set of codes that ralate to the last notice sent. IE if it is level 1 then the first notice was sent, level 2 = second notice, etc. Now your logic will show that it is at level 2 but according to the date it should be level 3, so send 3rd notice. You can also trap for when it should be at 3 but is actually at 2, and decide whether to send 3rd notice or 4th.

Roger_Ramjet

I use the 3rd method myself, which is a 'rule based' system.

Accounts have a status.
In the StatusCodes table, I have a series of true/false fields against each code. These fields are defined to record certain rules. EG if status is 'disputed' then PrintLetters = False, status='overdue' then PrintLetters=True, etc.
I have a whole series of 'rules like this: eg UserCanSet, UserCanChange, CashCheck, and even 'can this status be overwritten with that status'. When the user tries to change status these rules are used to determine a) if they can, and b) what they can change it to.

When I join the accounts table and the status table, each account now carries it's specific ruleset so eg my letter run just needs to check PrintLetters and time since last letter to determine whether to include each account.

emrys404

Thank you for all your help. I ended up doing something like that. My main problem was the records dissapearing if they fell out of a specific date range so i had the date ranges look for any record in its date range that had the proper number of reminders and below. then when the notification is generated it sets the number of reminders for the record to where it should be for that date range rather than the actual number of reminders.... confusing but it works for me.

Thanks again
-emrys

Weedpacket

If you were using a dbms that had stored procedures you could write a procedure that did all the work of counting reminders and due dates and so forth, then have your query call that. With such techniques, you can avoid having additional fields containing information that can be determined dynamically from fields already present, duplicating data and potentially raising synchronisation problems.