hi all, i've a question concerning the calculation of the number of days between 2 dates, and after searching the mailing list i'm still stuck, any help will be appreceiated :}
actually i've a table with a lot of rows (varying between 5 to 15 millions), each row has a unique id and 2 date: start_date and end_date (which are in fact datetime fields). i need to calculate the (integer) number of days between start_date and end_date for each row BUT without counting some dates (mostly sundays and some holidays) if they are present between start_date and end_date. i dont know if it's very clear (sorry, english is not my native language) so here's an example of what i'm trying actually : - i've created a table to store the dates i need to exclude : CREATE TABLE `TBL_EXCLUDE_DATE` ( `excl_date` date NOT NULL default '0000-00-00', `label` char(32) NOT NULL default '', PRIMARY KEY (`excl_date`) ) and populated it with all the sundays of year 2003 and some others dates like christmas, easter monday, etc etc i've also my main table with my millions of rows: CREATE TABLE `TBL_MAIN_DATE` ( `id` mediumint(9) unsigned NOT NULL auto_increment, `start_date` datetime NOT NULL, `end_date` datetime NOT NULL, PRIMARY KEY (`id`), KEY `start_date` (`start_date`), KEY `end_date` (`end_date`) ) i created also a third table for storing the number of days to exclude for each row: CREATE TABLE `TBL_COUNT_EXCLUDE` ( `id` mediumint(9) unsigned NOT NULL, `excl_count` tinyint unsigned NOT NULL, PRIMARY KEY (`id`) ) and i populate it with this query: INSERT INTO `TBL_COUNT_EXCLUDE` SELECT TEST.id, SUM(IF(EX.excl_date BETWEEN TEST.start_date AND TEST.end_date, 1, 0)) AS nb_jour FROM TBL_TEST AS TEST, SILOE_REF_EXCLUSION_DATE AS EX GROUP BY id ___________________________________________________________ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
