Find 'missing' dates

northerner

I have a transactional table which is being reported on. I need to make sure that data exists for every day between the start and end date (data is expected 7 days a week, and there can be many entries per day). Any days without data need to be highlighted on the report.

For example, if the report date filter was 1.7.10..5.7.10 and the entries in the table were as follows:

1.7.10
2.7.10
4.7.10
5.7.10

I would need to show 3.7.10 on the report, because there is no entry.

The start and end date of the report is generally going to be 1 year apart, and each day needs to be checked 3 times - once for each operational unit in the company (a value will be stored in the transactional table for the operational unit which can be filtered on).

What's a good way of coding this? I'm thinking about applying a filter to the (system) date table and working through each record, filtering the transactional table to the date, and marking records in the date table where no entries were found in the transactional table. Then building up a text string based on the marked entries. However I'm not convinced this is the best way - does anybody have any suggestions?

Many thanks.

kapamarou

I think this way is the best.

Since a date can have many entries, looping through the days would probably give you less loops than the other way round.

Loop the dates and do a SKIP if the filtered transaction table has entries (without looping through all, since one is enough ) to skip the date in question.

David_Singleton

northerner wrote:

... and marking records in the date table where no entries were found in the transactional table. Then building up a text string based on the marked entries.

This sounds way to complex.

As Kpamarou hints, the solution really depends on your data, specifically how selective the data will be based on an index on date.

To start with, the first thing you know is that any solution can give a maximum of 366 results, which is not much, so I would start there.

create a Temporary Table (ListOfDates) that has just one field of date (or more fields if you need more information later.
populate this table with the days of the year.
create an index on your source table with date as the key.
create a variable based on the Date table (DatesInCurrentYear).
Then do something like.

DatesInCurrentYear.setrange("Period Type",DatesInCurrentYear."Period Type"::date);
DatesInCurrentYear.setrange("Period Start",'Jan 1st','Dec 31st);
DatesInCurrentYear.FINDSET;
REPEAT
  SourceTable.Setrange(DatesInCurrentYear."Period Start");
  if not SourceTable.isempty then begin
    ListOfDates.Date := DatesInCurrentYear."Period Start";
    if ListOfDates.Delete then
      ;
  end;
UNTIL DatesInCurrentYear.next = 0;

Now you have a table populated with all your missing dates, and only 366 reads to the database, so should be very fast.

northerner

David / kapamarou

Thanks for your input; I'll go with the temporary table since I'll have more control over it than the system date table. The code sample is much appreciated :-)

David_Singleton

northerner wrote:

David / kapamarou

Thanks for your input; I'll go with the temporary table since I'll have more control over it than the system date table. The code sample is much appreciated :-)

You are welcome :thumbsup: , just make sure to let us know how you solved it in the end.

northerner

This is what I went with in the end, a function that returns a text string containing the dates when data is missing. I'm sure there are more elegant ways of coding it but this does the job for me!

The function is passed one parameter, StoreNo (Code 20), and returns Text 1024.

Name DataType Subtype Length
LastDate Date
MyDateTable Record Date TempData
SysDateTable Record Date
Footfall2 Record Footfall
Month Decimal
StartOfMonth Date
EndOfMonth Date
MissingText Text 1024
FirstDay Boolean
FirstMonth Boolean

//If report runs into the future, ignore future dates as they should have no data
IF EndDate[12] > TODAY THEN
  LastDate := CALCDATE('-1D',TODAY)
ELSE
  LastDate := EndDate[12];

//populate my temporary date table based on the system date table
SysDateTable.SETRANGE("Period Type",SysDateTable."Period Type"::Date);
SysDateTable.SETRANGE("Period Start",StartDate[1],LastDate);
IF SysDateTable.FINDSET THEN
  REPEAT
    MyDateTable.Date := SysDateTable."Period Start";
    MyDateTable."Store No." := StoreNo;
    MyDateTable.INSERT;
  UNTIL SysDateTable.NEXT = 0;

//delete dates from the temporary date table where data does exist
IF MyDateTable.FINDFIRST THEN
  REPEAT
    Footfall2.SETRANGE(Store,StoreNo);
    Footfall2.SETRANGE(Date,MyDateTable.Date);
    IF NOT Footfall2.ISEMPTY THEN
      MyDateTable.DELETE;
  UNTIL MyDateTable.NEXT = 0;

//to prevent overflow error if too many dates are missing
MyDateTable.SETRANGE("Store No.",StoreNo);
IF MyDateTable.COUNT > 100 THEN
  EXIT('Too many dates to list');

//build text string
FirstMonth := TRUE;
IF MyDateTable.FINDFIRST THEN
  REPEAT
    StartOfMonth := CALCDATE('-CM',MyDateTable.Date);
    EndOfMonth := CALCDATE('CM',MyDateTable.Date);
    MyDateTable.SETRANGE(Date,StartOfMonth,EndOfMonth);
    IF MyDateTable.FINDSET THEN
      BEGIN
        FirstDay := TRUE;
        IF NOT FirstMonth THEN
          MissingText := MissingText + '.  ';
        CLEAR(FirstMonth);
        MissingText := MissingText + FORMAT(MyDateTable.Date,0,'<Month Text>') + ': ';
        REPEAT
          IF NOT FirstDay THEN
            MissingText := MissingText + ', ';
          CLEAR(FirstDay);
          MissingText := MissingText + FORMAT(DATE2DMY(MyDateTable.Date,1));
        UNTIL MyDateTable.NEXT = 0;
        MyDateTable.DELETEALL;
        MyDateTable.RESET;
      END;
  UNTIL MyDateTable.NEXT = 0;
EXIT(MissingText);

It could be improved by giving a date range rather than individual dates. For example, July: 1 - 4, 6 is possibly better than July: 1, 2, 3, 4, 6. That might be a future tweak.