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]
