Hello Everyone,
I have the following 2 tables as follows:
Reminder Table
`reminder_id` int(10) unsigned NOT NULL auto_increment,
`username` varchar(50) default NULL,
`reminderdate` date default NULL,
`reminder` varchar(255) default NULL,
`autoclose` varchar(1) default NULL,
`recur` varchar(1) default NULL,
`occurence` int(3) default NULL,
`emailme` varchar(3) default NULL,
`emailtoperson1` varchar(100) default NULL,
`emailtoperson2` varchar(100) default NULL,
`emailtoperson3` varchar(100) default NULL,
`reminded` varchar(1) default NULL,
`status` varchar(10) default NULL,
PRIMARY KEY (`reminder_id`)
Occurence
`occurence_id` int(11) NOT NULL auto_increment,
`occurence` varchar(50) collate latin1_general_ci default NULL,
`periodtype` varchar(11) collate latin1_general_ci default NULL,
`period` int(2) default NULL,
PRIMARY KEY (`occurence_id`)
occurence.occurence_id relates to reminder.occurence
The occurence table stores the type of occurences available for example:
Occurence_ID Occurence Period Period Type
1 1 Week 7 Week
2 Quarterly 3 Month
3 Annual 1 Year
The idea of the occurence table is so that dates can be change by day, month or year and not having to go deep into mathematical calculations.
A sample data for reminder is as follows:
`reminder_id` 10
`username` defcon2000
`reminderdate` 2005-12-03
`reminder` "hello this is a test"
`autoclose` Y
`recur` Y
`occurence` 2 (meaning it will recur every quarter)
`emailme` Y
`emailtoperson1` "defcon2000@hammer.com"
`emailtoperson2` ""
`emailtoperson3` ""
`reminded` N
`status` "Open"
--------------------------------------------------------------------------------
The logic I am look for in an SQL format is:
SELECT * From reminder
WHERE "status" = "open" and "reminded" ="N"
DO THE FOLLOWING FOR EACH ROW ROUND
IF "reminderdate" = "today's date"
THEN "reminded" = "Y"
IF "reminderdate" > "7 days from today's date" and "reminded" = "Y" and "autoclose" = "Y" and "recur" = "N"
THEN "status" = "Closed"
ELSE
"status" = "Closed"
COPY RECORD into reminder
CHANGE "reminderdate" meet "occurrence" requirement,
SET "reminded" = "N"
SET "recur" = "Y",
SET "autoclose" = "Y"
SET "status" = "Open"
--------------------------------------------------------------------------------
I hope someone can help me as I am quite new to MySQL and esp SQL.
Thanks in advance.
Rgds,