Jump to content

Archived

This topic is now archived and is closed to further replies.

defcon2000

Reminder Database

Recommended Posts

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,

 

 

Share this post


Link to post
Share on other sites

You're going to have to break up each of those pieces of logic into SQL syntax, and try them in sequence. Post back when you've got that far, and we can walk you through the tricky parts.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.