Search for Missing Date

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I would like to create a query that would search for a
missing date in an imported table. I regularly import
payroll records and want to know if a day is missing.

Dave
 
Do you mean missing as to have a row where the field value for the date
field does not exist? If that is the case, you could do this:
Select * From YOURTABLENAME Where YOURDATEFIELD Is Null

Regards,

Sal Rosario
Database Cosultant in NJ
www.salrosario.com
 
To see if A date was missing, rather than WHICH date was
missing (a simple check before looking further...

I would write a query (Q1) to extract the min date,
another (Q2) to extract the max date, and a third to take
the difference between the two int(Q1) - int(Q2)
I would then compare this result to the result of

Select count(int(datefield)) from table group by int
(datefield)

This will give the number of distinct dates in the table.
If there are no missing dates, these two numbers should be
the same.
 
-----Original Message-----
Do you mean missing as to have a row where the field value for the date
field does not exist? If that is the case, you could do this:
Select * From YOURTABLENAME Where YOURDATEFIELD Is Null

Regards,

Sal Rosario
Database Cosultant in NJ
www.salrosario.com





I was trying to determine the actual calendar date
Click to expand...
missing. In other words, after I have downloaded all
records for 2003 I want to be able to determine if there
is a "hole" in the data (i.e. there are no records dated
June 3, 2003).
 
PERHAPS the following UNTESTED SQL will help

SELECT A.PayDate - 1 as MissingDate
FROM TblImport as A LEFT JOIN TblImport As B
ON A.Paydate = (B.PayDate -1)
WHERE B.PayDate Is Null
AND A.PayDate Between #1/2/2003# and #12/31/2003#

That assumes that Jan 1 2003 is there.
 
Back
Top