Identify Accounts with Inconsistent Pattern

[one2one]

I have a case where the client wants to know if there a way to catch or flag and account number that does not have daily activity. In the table below you can see that the account had some activity on the 09/01 – 09/04 and then again had some activity on the 09/15 – 09/18. So basically from 09/05-09/14 there was no activity and the clients wants to know how she can create a flag or an indicator that will let her know what this account does not have daily activity.
There will be multiple accounts and there are months that are 30, 31, 24, or 29, days

Can this be done ??


type account
number date
VATSBRNK 153910240974 000000005180 09/01/09
VATSBRNK 153910240974 000000005180 09/02/09
VATSBRNK 153910240974 000000005180 09/03/09
VATSBRNK 153910240974 000000005180 09/04/09
VATSBRNK 153910240974 000000005180 09/15/09
VATSBRNK 153910240974 000000005180 09/16/09
VATSBRNK 153910240974 000000005180 09/17/09
VATSBRNK 153910240974 000000005180 09/18/09
VATSBRNK 153910240974 000000005180 09/25/09
VATSBRNK 153910240974 000000005180 09/26/09
VATSBRNK 153910240974 000000005180 09/28/09
VATSBRNK 153910240974 000000005180 09/29/09
VATSBRNK 153910240974 000000005180 09/30/09

[Atli]

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.