Link to home
Start Free TrialLog in
Avatar of mkirank
mkirank

asked on

MySQL 4.0 DayOfWeek pattern match query

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.
Avatar of snoyes_jw
snoyes_jw
Flag of United States of America image

1) is easy enough.
SELECT LEAST(start_date, '2006-12-08') AS lowEnd, GREATEST(end_date, '2006-12-13') AS highEnd

I'll have to think about 2) for a bit.
ASKER CERTIFIED SOLUTION
Avatar of snoyes_jw
snoyes_jw
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial