Mark, You can find your first missing date in MySQL 4.1.0(alpha) or higher, using sub-selects, something like this:
SELECT MIN(DATEADD(LOAD_DATE,1)) AS MISSING_DATE FROM load_cntl AS A WHERE DATEDIFF(NOW(), LOAD_DATE) < 30 AND LOAD_DATE <> (SELECT MAX(LOAD_DATE) FROM load_cntl AS B) AND NOT EXISTS ( SELECT * FROM load_cntl AS B WHERE B.LOAD_DATE = DATEADD(A.LOAD_DATE, 1) ) Good Luck! Charlie [EMAIL PROTECTED]/Shield/Michigan > From: Mark Marshall Date: December 10 2003 6:01pm > Subject: SQL Statement Help - Is this possible? > > I have a database table that has one row entered every day by the user. > If the user skips a day, it throws off our monthly totals. Assume a > table with four columns: > > id (which is the key), date (yyyy-mm-dd), data1 (int), data2 (int) > > Is there a way to say "Select the first missing date in the last 30 > days from this table"? > > I know I can select all the rows, ordered by date, and loop through > them with PHP to look for first one that's missing. But that seems > inefficient. And I know that once in place, in theory there should > never be any more missing dates if I just grab the max date and add one. > But I don't want to do that. I want the page to be SURE there are no > missing dates in the last 30 days. > > Thanks, > Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]