Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

MySQL 4.0 DayOfWeek pattern match query

Avatar of mkirank
mkirank asked on
MySQL Server
2 Comments1 Solution902 ViewsLast Modified:
Here is the problem related to Day-of-Week Pattern :

The table structure as below:
------------------------------
CREATE TABLE `dow_table` (                  
    `id` int(10) unsigned NOT NULL auto_increment,    
    `start_date` date NOT NULL default '0000-00-00',  
    `stop_date` date NOT NULL default '0000-00-00',  
    `dow` varchar(7) NOT NULL default '',
    PRIMARY KEY  (`id`),                              
) TYPE=MyISAM                                      

Here is the data:
-----------------
insert into `dow_table` values (1,'2006-12-10','2006-12-15','MWF');
insert into `dow_table` values (2,'2006-12-13','2006-12-20','TRSN');
insert into `dow_table` values (3,'2006-12-10','2006-12-31','MTWRFSN');
insert into `dow_table` values (4,'2006-12-20','2006-12-30','M');
insert into `dow_table` values (5,'2006-12-01','2006-12-10','SN');

dow - Day of Week Pattern
M = Monday, T = Tuesday, W = Wednesday, R = Thursday, F = Friday, S = Saturday, N = Sunday

I do a search on this table using Search_start = '2006-12-08', Search_stop  = '2006-12-13'

SELECT * FROM dow_table
WHERE start_date<='2006-12-13' and stop_date >='2006-12-08';

I get all the records whose date-range overlap the searched date range. Till here I get it fine.

The next few steps (which I need help) are as below:
(1) Find out the overlapped dates.
    (Ex. dow_table date range is '2006-12-10' to '2006-12-15'. The searched date range is '2006-12-08' to '2006-12-13', so this give us the overlap date range from '2006-12-10' to '2006-12-13')
(2) Check if the DayOfWeek pattern as in the 'dow' field matches with any of the overlapped date's DayOfWeek.
(3) If any one DayOfWeek matches then show the record in the result.

I modified the query a bit, and this is as far I could go:

SELECT * FROM dow_table
WHERE (
        start_date<='2006-12-13' and stop_date >='2006-12-08'
        and dow='MTWRFSN'
       )

I get the records whose DayOfWeek pattern is the full week, the overlapped date's DayOfWeek will surely have one of these days.

I am still not able to get the 3 steps which are described above.

The MySQL version is 4.0.24 and it does not have DATEDIFF, DATE_SUBTRACT function support.

Any help will be greatly appreciated.
ASKER CERTIFIED SOLUTION
Avatar of snoyes_jw
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answers