Dear all, anyone knows why the following is going wrong (I expect 1 in the columns Total and Open)? I suppose it is because MySQL is internally casting the IF expression (which is DATE vs DATE or DATE vs DATETIME) internally into a STRING before comparison, but not sure about that (String) '2003-10-06' >= (String) '2003-10-06' ==> true (String) '2003-10-06' >= (String) '2003-10-06 00:00:00' ===> false If the IF statement really casts the two dates (DATE, DATETIME) into a STRING, i think this is a bug. And a pretty nasty one. NOTE: You have to run the SQL statement on the next day of the entered date in the table. So like the date is now you have to run the script on 2003/10/07 (octobre 7, 2003). Again to point out the problem: Apparently it is a difference to substract 24 HOURS or 1 DAY from a DATE (not DATETIME) and then using this result in an IF clause (CASE too??, ifnull()???, any other expression?). Thanx for any feedback! Cheers /rudy ----------------------------------------------------------------------------- HERE my Table definition: CREATE TABLE `tpr` ( `tprnr` int(10) unsigned NOT NULL auto_increment, `projectid` int(10) unsigned NOT NULL default '0', `severityid` char(3) NOT NULL default 'L', `statusid` char(3) NOT NULL default 'ENT', `entered` date NOT NULL default '0000-00-00', `entered_by` varchar(20) NOT NULL default '', `assigned` date default NULL, `assigned_to` varchar(20) default NULL, `fixed` date default NULL, `released` date default NULL, `released_by` varchar(20) default NULL, `tested` date default NULL, `tested_by` varchar(20) default NULL, `finished` date default NULL, `closed` date default NULL, `quote` int(10) unsigned default NULL, `quoted` date default NULL, `quoted_by` varchar(20) default NULL, `screenid` int(10) unsigned default NULL, `screenname` varchar(80) default NULL, `subject` varchar(80) default NULL, `description` text, `solution` text, `testreport` text, `is_parked` char(1) NOT NULL default 'N', `creusr` varchar(20) default NULL, `credat` date default NULL, `modusr` varchar(20) default NULL, `moddat` date default NULL, PRIMARY KEY (`tprnr`), KEY `idx1` (`projectid`) ) TYPE=MyISAM mysql> select * from tpr where tprnr = 368\G *************************** 1. row *************************** tprnr: 368 projectid: 1 severityid: H statusid: ENT entered: 2003-10-06 entered_by: rmetzger assigned: NULL assigned_to: NULL fixed: NULL released: NULL released_by: NULL tested: NULL tested_by: NULL finished: NULL closed: NULL quote: NULL quoted: NULL quoted_by: NULL screenid: NULL screenname: NULL subject: finish invoicing module description: - create summary reports (total invoice amount per month, solution: NULL testreport: NULL is_parked: N creusr: rmetzger credat: 2003-10-06 modusr: rmetzger moddat: 2003-10-06 1 row in set (0.00 sec) ----------------------------------------------------------------------------- The statement which is going WRONG: SELECT s.statusid, s.status, count(tpr.tprnr),entered, sum( if ( ( CASE WHEN s.statusid = 'ENT' THEN tpr.entered WHEN s.statusid = 'ASS' THEN tpr.assigned WHEN s.statusid = 'FIX' THEN tpr.fixed WHEN s.statusid = 'REL' THEN tpr.released WHEN s.statusid = 'TOK' THEN tpr.tested WHEN s.statusid = 'TNO' THEN tpr.tested WHEN s.statusid = 'FIN' THEN tpr.finished WHEN s.statusid = 'CLS' THEN tpr.closed ELSE '1990-01-01' /*IW,IT*/ END ) >= date_sub( curdate(), INTERVAL 24 HOUR ), 1, 0 ) ) FROM status s LEFT JOIN tpr ON tpr.statusid = s.statusid AND tpr.projectid = 1 AND tpr.is_parked = 'N' AND tpr.tprnr = 368 GROUP BY s.sort_idx, s.statusid, s.status ORDER BY s.sort_idx ----------------------------------------------------------------------------- The statement which is WORKING: SELECT s.statusid, s.status, count(tpr.tprnr),entered, sum( if ( ( CASE WHEN s.statusid = 'ENT' THEN tpr.entered WHEN s.statusid = 'ASS' THEN tpr.assigned WHEN s.statusid = 'FIX' THEN tpr.fixed WHEN s.statusid = 'REL' THEN tpr.released WHEN s.statusid = 'TOK' THEN tpr.tested WHEN s.statusid = 'TNO' THEN tpr.tested WHEN s.statusid = 'FIN' THEN tpr.finished WHEN s.statusid = 'CLS' THEN tpr.closed ELSE '1990-01-01' /*IW,IT*/ END ) >= date_sub( curdate(), INTERVAL 1 DAY ), 1, 0 ) ) FROM status s LEFT JOIN tpr ON tpr.statusid = s.statusid AND tpr.projectid = 1 AND tpr.is_parked = 'N' AND tpr.tprnr = 368 GROUP BY s.sort_idx, s.statusid, s.status ORDER BY s.sort_idx
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
