Solution to Date Range Problem Using Database Technology

This is a solution to finding a set of date ranges where the start date is the Sunday of the last full week (to Friday) of the previous month given and the end date is the day before Sunday of the last full week of the month (to Friday).
The first thing I needed to solve this problem was a set of dates. I wanted to start June 30, 2008 and fill 10 years worth of data. I wanted to be sure and include the important information including Year, Month, Day, Weekday, and timestamp. So here's how I started in mysql 5.0.51b-community-nt:

create database dates;
use dates
CREATE TABLE `dates` (
`Year` int(4) unsigned default NULL,
`Month` int(2) unsigned default NULL,
`Day` int(2) unsigned default NULL,
`Weekday` int(1) unsigned default NULL,
`TS` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
) ENGINE=MyISAM;

Delimiter //
CREATE PROCEDURE `FillDates`(IN Start timestamp)
BEGIN
declare Ct int default 0;
WHILE Ct < 3650 DO
insert into dates select YEAR(Start),MONTH(Start),DAYOFMONTH(Start),DAYOFWEEK(St
art),Start;
SET Ct = Ct + 1;
Set Start = Start + interval 1 day;
END WHILE;
END //

Delimiter ;

@Today = 20080630000000;
FillDates(@Today);


Now that I had the set of dates with all necessary data, I created the date_range table itself and a month table since I was receiving the month by short name i.e. "AUG". I went ahead and datafilled the month table as well:

CREATE TABLE `date_range` (
`Year` int(4) unsigned default NULL,
`ID` int(2) unsigned default NULL,
`Month` varchar(3) default NULL,
`MonthStart_TS` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`FROMDATE` timestamp NOT NULL default '0000-00-00 00:00:00',
`TODATE` timestamp NOT NULL default '0000-00-00 00:00:00'
) ENGINE=myisam;

CREATE TABLE `month` (
`Month` varchar(3) default NULL,
`ID` int(2) default NULL
) ENGINE=MyISAM;

insert into month values ('JAN',1);
insert into month values ('FEB',2);
insert into month values ('MAR',3);
insert into month values ('APR',4);
insert into month values ('MAY',5);
insert into month values ('JUN',6);
insert into month values ('JUL',7);
insert into month values ('AUG',8);
insert into month values ('SEP',9);
insert into month values ('OCT',10);
insert into month values ('NOV',11);
insert into month values ('DEC',12);


Next I needed a set of unique Year,Month pairs with associated values to start the date_range datafill:

insert into date_range (Year,ID,Month,MonthStart_TS)
select Year,dates.Month,month.Month,TS as MonthStart_TS
from dates left join month on dates.Month =month.ID
where Day=1;


Now I create start and end tables to store the respective start and end timestamps. Lastly I update date_range table with a join on those tables:

create table start
select r.Year, r.ID, r.Month,max(d.TS) as Start_TS from date_range as r left outer join
dates as d on 1 where Weekday = 1 and d.TS < r.MonthStart_TS - interval 5 day
group by r.Year,r.ID;

create table end
select r.Year, r.ID, r.Month,max(d.TS) + interval 1 day - interval 1 second as End_TS
from date_range as r left outer join dates as d on 1
where Weekday = 7 and d.TS < r.MonthStart_TS + interval 1 month - interval 6 day
group by r.Year,r.ID;

update date_range
inner join start on date_range.Year = start.Year and date_range.ID = start.ID
inner join end on date_range.Year = end.Year and date_range.ID = end.ID
set FROMDATE=Start_TS,TODATE=End_TS,MonthStart_TS=MonthStart_TS;


Note that the data for JUL 2008 will not be entered because we didn't start the date range with an early enough date to get the start for that month. In my case I didn't need data for that month. The final data looks like this:
YearIDMonthMonthStart_TSFROMDATETODATE
20087JUL2008-07-01 00:00:000000-00-00 00:00:000000-00-00 00:00:00
20088AUG2008-08-01 00:00:002008-07-20 00:00:002008-08-23 23:59:59
20089SEP2008-09-01 00:00:002008-08-24 00:00:002008-09-20 23:59:59
200810OCT2008-10-01 00:00:002008-09-21 00:00:002008-10-25 23:59:59
200811NOV2008-11-01 00:00:002008-10-26 00:00:002008-11-22 23:59:59
200812DEC2008-12-01 00:00:002008-11-23 00:00:002008-12-20 23:59:59
20091JAN2009-01-01 00:00:002008-12-21 00:00:002009-01-24 23:59:59
20092FEB2009-02-01 00:00:002009-01-25 00:00:002009-02-21 23:59:59
20093MAR2009-03-01 00:00:002009-02-22 00:00:002009-03-21 23:59:59
20094APR2009-04-01 00:00:002009-03-22 00:00:002009-04-18 23:59:59
20095MAY2009-05-01 00:00:002009-04-19 00:00:002009-05-23 23:59:59
20096JUN2009-06-01 00:00:002009-05-24 00:00:002009-06-20 23:59:59
20097JUL2009-07-01 00:00:002009-06-21 00:00:002009-07-25 23:59:59
20098AUG2009-08-01 00:00:002009-07-26 00:00:002009-08-22 23:59:59
20099SEP2009-09-01 00:00:002009-08-23 00:00:002009-09-19 23:59:59
200910OCT2009-10-01 00:00:002009-09-20 00:00:002009-10-24 23:59:59
200911NOV2009-11-01 00:00:002009-10-25 00:00:002009-11-21 23:59:59
200912DEC2009-12-01 00:00:002009-11-22 00:00:002009-12-19 23:59:59
20101JAN2010-01-01 00:00:002009-12-20 00:00:002010-01-23 23:59:59
20102FEB2010-02-01 00:00:002010-01-24 00:00:002010-02-20 23:59:59
20103MAR2010-03-01 00:00:002010-02-21 00:00:002010-03-20 23:59:59
20104APR2010-04-01 00:00:002010-03-21 00:00:002010-04-24 23:59:59
20105MAY2010-05-01 00:00:002010-04-25 00:00:002010-05-22 23:59:59
20106JUN2010-06-01 00:00:002010-05-23 00:00:002010-06-19 23:59:59
20107JUL2010-07-01 00:00:002010-06-20 00:00:002010-07-24 23:59:59
20108AUG2010-08-01 00:00:002010-07-25 00:00:002010-08-21 23:59:59
20109SEP2010-09-01 00:00:002010-08-22 00:00:002010-09-18 23:59:59
201010OCT2010-10-01 00:00:002010-09-19 00:00:002010-10-23 23:59:59
201011NOV2010-11-01 00:00:002010-10-24 00:00:002010-11-20 23:59:59
201012DEC2010-12-01 00:00:002010-11-21 00:00:002010-12-25 23:59:59
20111JAN2011-01-01 00:00:002010-12-26 00:00:002011-01-22 23:59:59
20112FEB2011-02-01 00:00:002011-01-23 00:00:002011-02-19 23:59:59
20113MAR2011-03-01 00:00:002011-02-20 00:00:002011-03-19 23:59:59
20114APR2011-04-01 00:00:002011-03-20 00:00:002011-04-23 23:59:59
20115MAY2011-05-01 00:00:002011-04-24 00:00:002011-05-21 23:59:59
20116JUN2011-06-01 00:00:002011-05-22 00:00:002011-06-18 23:59:59
20117JUL2011-07-01 00:00:002011-06-19 00:00:002011-07-23 23:59:59
20118AUG2011-08-01 00:00:002011-07-24 00:00:002011-08-20 23:59:59
20119SEP2011-09-01 00:00:002011-08-21 00:00:002011-09-24 23:59:59
201110OCT2011-10-01 00:00:002011-09-25 00:00:002011-10-22 23:59:59
201111NOV2011-11-01 00:00:002011-10-23 00:00:002011-11-19 23:59:59
201112DEC2011-12-01 00:00:002011-11-20 00:00:002011-12-24 23:59:59
20121JAN2012-01-01 00:00:002011-12-25 00:00:002012-01-21 23:59:59
20122FEB2012-02-01 00:00:002012-01-22 00:00:002012-02-18 23:59:59
20123MAR2012-03-01 00:00:002012-02-19 00:00:002012-03-24 23:59:59
20124APR2012-04-01 00:00:002012-03-25 00:00:002012-04-21 23:59:59
20125MAY2012-05-01 00:00:002012-04-22 00:00:002012-05-19 23:59:59
20126JUN2012-06-01 00:00:002012-05-20 00:00:002012-06-23 23:59:59
20127JUL2012-07-01 00:00:002012-06-24 00:00:002012-07-21 23:59:59
20128AUG2012-08-01 00:00:002012-07-22 00:00:002012-08-25 23:59:59
20129SEP2012-09-01 00:00:002012-08-26 00:00:002012-09-22 23:59:59
201210OCT2012-10-01 00:00:002012-09-23 00:00:002012-10-20 23:59:59
201211NOV2012-11-01 00:00:002012-10-21 00:00:002012-11-24 23:59:59
201212DEC2012-12-01 00:00:002012-11-25 00:00:002012-12-22 23:59:59
20131JAN2013-01-01 00:00:002012-12-23 00:00:002013-01-19 23:59:59
20132FEB2013-02-01 00:00:002013-01-20 00:00:002013-02-16 23:59:59
20133MAR2013-03-01 00:00:002013-02-17 00:00:002013-03-23 23:59:59
20134APR2013-04-01 00:00:002013-03-24 00:00:002013-04-20 23:59:59
20135MAY2013-05-01 00:00:002013-04-21 00:00:002013-05-25 23:59:59
20136JUN2013-06-01 00:00:002013-05-26 00:00:002013-06-22 23:59:59
20137JUL2013-07-01 00:00:002013-06-23 00:00:002013-07-20 23:59:59
20138AUG2013-08-01 00:00:002013-07-21 00:00:002013-08-24 23:59:59
20139SEP2013-09-01 00:00:002013-08-25 00:00:002013-09-21 23:59:59
201310OCT2013-10-01 00:00:002013-09-22 00:00:002013-10-19 23:59:59
201311NOV2013-11-01 00:00:002013-10-20 00:00:002013-11-23 23:59:59
201312DEC2013-12-01 00:00:002013-11-24 00:00:002013-12-21 23:59:59
20141JAN2014-01-01 00:00:002013-12-22 00:00:002014-01-25 23:59:59
20142FEB2014-02-01 00:00:002014-01-26 00:00:002014-02-22 23:59:59
20143MAR2014-03-01 00:00:002014-02-23 00:00:002014-03-22 23:59:59
20144APR2014-04-01 00:00:002014-03-23 00:00:002014-04-19 23:59:59
20145MAY2014-05-01 00:00:002014-04-20 00:00:002014-05-24 23:59:59
20146JUN2014-06-01 00:00:002014-05-25 00:00:002014-06-21 23:59:59
20147JUL2014-07-01 00:00:002014-06-22 00:00:002014-07-19 23:59:59
20148AUG2014-08-01 00:00:002014-07-20 00:00:002014-08-23 23:59:59
20149SEP2014-09-01 00:00:002014-08-24 00:00:002014-09-20 23:59:59
201410OCT2014-10-01 00:00:002014-09-21 00:00:002014-10-25 23:59:59
201411NOV2014-11-01 00:00:002014-10-26 00:00:002014-11-22 23:59:59
201412DEC2014-12-01 00:00:002014-11-23 00:00:002014-12-20 23:59:59
20151JAN2015-01-01 00:00:002014-12-21 00:00:002015-01-24 23:59:59
20152FEB2015-02-01 00:00:002015-01-25 00:00:002015-02-21 23:59:59
20153MAR2015-03-01 00:00:002015-02-22 00:00:002015-03-21 23:59:59
20154APR2015-04-01 00:00:002015-03-22 00:00:002015-04-18 23:59:59
20155MAY2015-05-01 00:00:002015-04-19 00:00:002015-05-23 23:59:59
20156JUN2015-06-01 00:00:002015-05-24 00:00:002015-06-20 23:59:59
20157JUL2015-07-01 00:00:002015-06-21 00:00:002015-07-25 23:59:59
20158AUG2015-08-01 00:00:002015-07-26 00:00:002015-08-22 23:59:59
20159SEP2015-09-01 00:00:002015-08-23 00:00:002015-09-19 23:59:59
201510OCT2015-10-01 00:00:002015-09-20 00:00:002015-10-24 23:59:59
201511NOV2015-11-01 00:00:002015-10-25 00:00:002015-11-21 23:59:59
201512DEC2015-12-01 00:00:002015-11-22 00:00:002015-12-19 23:59:59
20161JAN2016-01-01 00:00:002015-12-20 00:00:002016-01-23 23:59:59
20162FEB2016-02-01 00:00:002016-01-24 00:00:002016-02-20 23:59:59
20163MAR2016-03-01 00:00:002016-02-21 00:00:002016-03-19 23:59:59
20164APR2016-04-01 00:00:002016-03-20 00:00:002016-04-23 23:59:59
20165MAY2016-05-01 00:00:002016-04-24 00:00:002016-05-21 23:59:59
20166JUN2016-06-01 00:00:002016-05-22 00:00:002016-06-18 23:59:59
20167JUL2016-07-01 00:00:002016-06-19 00:00:002016-07-23 23:59:59
20168AUG2016-08-01 00:00:002016-07-24 00:00:002016-08-20 23:59:59
20169SEP2016-09-01 00:00:002016-08-21 00:00:002016-09-24 23:59:59
201610OCT2016-10-01 00:00:002016-09-25 00:00:002016-10-22 23:59:59
201611NOV2016-11-01 00:00:002016-10-23 00:00:002016-11-19 23:59:59
201612DEC2016-12-01 00:00:002016-11-20 00:00:002016-12-24 23:59:59
20171JAN2017-01-01 00:00:002016-12-25 00:00:002017-01-21 23:59:59
20172FEB2017-02-01 00:00:002017-01-22 00:00:002017-02-18 23:59:59
20173MAR2017-03-01 00:00:002017-02-19 00:00:002017-03-25 23:59:59
20174APR2017-04-01 00:00:002017-03-26 00:00:002017-04-22 23:59:59
20175MAY2017-05-01 00:00:002017-04-23 00:00:002017-05-20 23:59:59
20176JUN2017-06-01 00:00:002017-05-21 00:00:002017-06-24 23:59:59
20177JUL2017-07-01 00:00:002017-06-25 00:00:002017-07-22 23:59:59
20178AUG2017-08-01 00:00:002017-07-23 00:00:002017-08-19 23:59:59
20179SEP2017-09-01 00:00:002017-08-20 00:00:002017-09-23 23:59:59
201710OCT2017-10-01 00:00:002017-09-24 00:00:002017-10-21 23:59:59
201711NOV2017-11-01 00:00:002017-10-22 00:00:002017-11-18 23:59:59
201712DEC2017-12-01 00:00:002017-11-19 00:00:002017-12-23 23:59:59
20181JAN2018-01-01 00:00:002017-12-24 00:00:002018-01-20 23:59:59
20182FEB2018-02-01 00:00:002018-01-21 00:00:002018-02-17 23:59:59
20183MAR2018-03-01 00:00:002018-02-18 00:00:002018-03-24 23:59:59
20184APR2018-04-01 00:00:002018-03-25 00:00:002018-04-21 23:59:59
20185MAY2018-05-01 00:00:002018-04-22 00:00:002018-05-19 23:59:59
20186JUN2018-06-01 00:00:002018-05-20 00:00:002018-06-23 23:59:59

I hope you enjoyed this article. Feel free to contact me with any constructive comments, questions, or remarks.
Jeff Doak

www.softfrontiers.com
Lunarpages.com Web Hosting