We help IT Professionals succeed at work.

Find missing date(s)

nike_golf
nike_golf asked
on
202 Views
Last Modified: 2012-05-05
I'm looking for some ideas on what the best way would be for me to identify missing load dates from a database we have.

Essentially we load data to a MYSQL database and there is a field marked with the date that it was loaded, called what else, 'loaddate'.

I want to be able to identify if a 'loaddate' has been missed. I would assume this will involve comparing the actual calendar to the first 'loaddate' in the DB and the last 'loaddate' in the DB and finding any missing dates in between those to dates...

Any ideas would be appreciated.

NG,
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Is it better to look for gaps in the data or to look to the calendar and run some comparison? I'm just thinking those odd years, leap year, etc. might cause a problem?
CERTIFIED EXPERT
Top Expert 2010

Commented:
nike_golf,
> Is it better to look for gaps in the data or to look to the calendar
> and run some comparison? I'm just thinking those odd years, leap year,
> etc. might cause a problem?

If you have a "calendar" table that lists all the dates you need, then a better approach would be to use that
calendar to generate a query of valid dates that are missing (using an outer join to the load date table, and
setting the criteria for load date to Null).

Regards,

Patrick
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
there are two approaches I might take:
1. to determine if any dates have been missed, run one query to find the min and max dates in your table.  Convert these dates to LONG and subtract the start date from the end date, alternatively, use the DateDiff("d", , ) function.  Then run a Count(*) query to get the number of rows.  Comparing your days difference with the count of rows should result in the quickest answer of whether you've missed any loads.

Note: If you have multiple loads in a day, for do-overs, this will not work.

2. Perform a matching merge of the load dates with a sequence of dates you create programmatically.

Example:
Dim dtCalendar As Date
rsLoadDates = .openrecordset("Select Distinct loaddate from mytable")
dtCalendar = CLng(rsLoadDates!loaddate)
Do Until rsLoadDates.EOF
  Select Case dtCalendar
    Case < CLng(rsLoadDates!loaddate)
      debug.print "Missing date: ", dtCalendar
      dtCalendar = dtCalendar  + 1
    Case > CLng(rsLoadDates!loaddate)
      rsLoadDates.MoveNext
    Case Else     'nothing missing, move both forward 1 rec / day
      rsLoadDates.MoveNext
      dtCalendar = dtCalendar  + 1
  End Select
Loop

Note: The CLng() is not required if there is no time portion of the longdate column data

Author

Commented:
"If you have a "calendar" table that lists all the dates you need, then a better approach would be to use that
calendar to generate a query of valid dates that are missing (using an outer join to the load date table, and
setting the criteria for load date to Null)."

No, no calendar table...


"1. to determine if any dates have been missed, run one query to find the min and max dates in your table.  Convert these dates to LONG and subtract the start date from the end date, alternatively, use the DateDiff("d", , ) function.  Then run a Count(*) query to get the number of rows.  Comparing your days difference with the count of rows should result in the quickest answer of whether you've missed any loads."

This would just give a number of missing days as opposed to the actual day(s) that are missing. The end user will have a problem ID'g the missing dates.

"2...."

That would also require a table with dates correct?

hmmmm.....

aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
no.  You are generating the dates programmatically in the dtCalendar variable.  Pass the data once and you are done.

If you need to do this often, then having a date table should be more efficient.  If you store the time of day with the loaddate, you will need to remove the time portion when you join the two tables.

Author

Commented:
Appreciate the suggestions but ended up writing a function to take care of it.


'***************************************************************************************
'rs = recordset of unique dates in DB
'Min/Max = Calendar Dates to search for missing Load_Date. This date format is 20060124
'***************************************************************************************

Private Function CalendarDates(MinDate, MaxDate, rs As Recordset) As Variant
Dim x As Date
Dim calendar As New Collection
Dim arrMissing() As Variant
Dim notDup As Boolean

On Error GoTo dateExists:

'Create collection of Load_Dates
Do Until rs.EOF
    calendar.Add DateSerial(Left(rs!Import_Date, 4), Mid(rs!Import_Date, 5, 2), Right(rs!Import_Date, 2)), CStr(DateSerial(Left(rs!Import_Date, 4), Mid(rs!Import_Date, 5, 2), Right(rs!Import_Date, 2)))
    rs.MoveNext
Loop

ReDim arrMissing(0)
'Expect all duplicate dates to error leaving only the missing dates added to the array
x = DateSerial(Left(MinDate, 4), Mid(MinDate, 5, 2), Right(MinDate, 2))
Do Until x > DateSerial(Left(MaxDate, 4), Mid(MaxDate, 5, 2), Right(MaxDate, 2))
    notDup = True
    calendar.Add x, CStr(x)
    If notDup Then
        arrMissing(UBound(arrMissing)) = x
        ReDim Preserve arrMissing(UBound(arrMissing()) + 1)
    End If
    x = x + 1
Loop

'Remove last item in array before passing on
ReDim Preserve arrMissing(UBound(arrMissing()) - 1)

CalendarDates = arrMissing()
Exit Function

dateExists:
    notDup = False
    Resume Next
   
End Function
Social distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Good suggestion, thanks.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.