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-1 5','MWF');
insert into `dow_table` values (2,'2006-12-13','2006-12-2 0','TRSN') ;
insert into `dow_table` values (3,'2006-12-10','2006-12-3 1','MTWRFS N');
insert into `dow_table` values (4,'2006-12-20','2006-12-3 0','M');
insert into `dow_table` values (5,'2006-12-01','2006-12-1 0','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.
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-1
insert into `dow_table` values (2,'2006-12-13','2006-12-2
insert into `dow_table` values (3,'2006-12-10','2006-12-3
insert into `dow_table` values (4,'2006-12-20','2006-12-3
insert into `dow_table` values (5,'2006-12-01','2006-12-1
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.