Hey.
Sure, this can be done.
Assuming a table like this:
- +--------+------------+
-
| client | date |
-
+--------+------------+
-
| No1 | 2009-09-27 |
-
| No1 | 2009-09-28 |
-
| No1 | 2009-09-30 |
-
| No1 | 2009-10-01 |
-
+--------+------------+
You can see there is a date missing there, 2009-09-29.
To determine the number of missing dates between the first date listed and the last date listed, you could calculate the number of dates actually recorded, and get the number of dates that
should be recorded.
The difference between the two would give you the value you need
- SELECT
-
`targetCount`-`actualCount` AS `dates missing`
-
FROM (
-
SELECT
-
COUNT(DISTINCT `date`) AS `actualCount`,
-
MAX(TO_DAYS(`date`)) - MIN(TO_DAYS(`date`)) + 1 AS `targetCount`
-
FROM `test`
-
WHERE `client` = 'No1'
-
) AS `d`;
That would give you the number of days missing, which would be 1 in this case.
