Finding Missing Dates

[OU18]

All,

Is there a query that I can write that will take a field that contains a standardized date and find the one that is missing

IE

Acct ID Accrual Month
205 1/1/2004
205 2/1/2004
205 3/1/2004
205 5/1/2004
205 6/1/2004
206 1/1/2004
206 3/1/2004

So in the example above, it would find that for ACCTID 205 4/1/2004 and Acct ID 206 2/1/04 is missing from the file

Thanks for the help all

 

[PHV]

Something like this ?
SELECT A.AcctID, A.[Accrual Month], Max(B.[Accrual Month]) AS PreviousMonth
FROM yourTable A INNER JOIN yourTable B
ON A.AcctID = B.AcctID AND A.[Accrual Month] > B.[Accrual Month]
GROUP BY A.AcctID, A.[Accrual Month]
HAVING (A.[Accrual Month] - Max(B.[Accrual Month])) > 31;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

STEP 1: Create a table called INTEGERS with one field called NUM (type Long). Create 10 rows in that table with the integers 0 to 9.

STEP 2: Create a query called INTEGERSIIII that looks like this

SELECT 10000*tenthousands.num + 1000*thousands.num + 100*hundreds.num + 10*tens.num + units.num AS iiii
FROM integers AS tenthousands, integers AS thousands, integers AS hundreds, integers AS tens, integers AS units
ORDER BY 1;

STEP 3: Create the query

SELECT cDate(I.iiii) As MissingDate
FROM myTable AS B RIGHT JOIN IntegersIIII AS I ON Int(B.myDate) = I.iiii
WHERE B.myDate  IS NULL 
      AND I.iiii BETWEEN (Select MIN( Int(myDate)) From myTable)
                     AND (Select MAX( Int(myDate)) From myTable)
ORDER BY I.iiii;

That will give you the missing dates.

With apologies to Rudy (r937) for this trick.

 

[OU18]

PHV,
Your solution runs quickly doesnt produce the final information I need. It gives me the Accrual Month and then the month just before it. How can I now come up with the Actual missing date.

Golom,
Your solution looks very detailed and looks as though it would work great, Yet it takes forever to run. Maybe I am doing something wrong.

Thanks

 

[Golom]

Seems odd. A slightly altered version of this (finding missing numbers rather than missing dates) runs in about 8 seconds on my system searching a table of about 99,000 rows.

Have you checked that the date field that you are filtering on is indexed? That will certainly slow it down if it isn't.

 

[OU18]

I will give that a shot and see if that helps it out

 

[PHV]

And what about this ?
SELECT A.AcctID, DateAdd("m", -1, A.[Accrual Month]) AS MissingMonth
FROM yourTable A INNER JOIN yourTable B
ON A.AcctID = B.AcctID AND A.[Accrual Month] > B.[Accrual Month]
GROUP BY A.AcctID, A.[Accrual Month]
HAVING (A.[Accrual Month] - Max(B.[Accrual Month])) > 31;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OU18]

Worked like a charm PHV, thank you much

 

[OU18]

PHV,
Is there a way to modify this query so that it will also look forward. IE. The last Accrual Date may be 3/1/2005. But the current date is June 8, 2005. According to the query listed above, it will show any months that are missing Prior to 3/1/2005 but will not show the missing months of 4/1/2005 or 5/1/2005 or 6/1/2005. Does that make sense.

Thanks for the help.

 

[OhioSteve]

I have an alternate solution.

1) Create a table that contains a record for each date. It is very easy to do this with excel's help. Enter the first three dates in a1 to a3, then select it all and drag down. Excel will be smart enough to fill in the next days.

2) Copy the date range to the clipboard. Then go to your Access DB and choose the table area. Right-click directly inside the area that holds the tables. Then paste. Access creates the table.

3) Use the "find unmatched" query wizard to find the unmatched dates, or adapt the sql below. In this sample, I called the table with all of the dates "allDates" and I called its field "d". I called the primary table "table1" and its date field is service date.

SELECT allDates.d
FROM allDates LEFT JOIN Table1 ON allDates.d = Table1.serviceDate
WHERE (((Table1.serviceDate) Is Null));

 

[thomsond]

Hello, this is a message to Golom.

I have been reading your advice for this problem and i have something similar. I am attempting to locate missing numbers from a field.

In one of your suggestion you mention the following:

"Seems odd. A slightly altered version of this (finding missing numbers rather than missing dates) runs in about 8 seconds on my system searching a table of about 99,000 rows."

This sounds like the solution to what i am looking for.

Any help with this would be greatly appreciated.