Month date range for missing data

[Moongirl]

I have a table that holds coverage information. An example is
effective date 11/1/2002
termed date 3/31/2003

Another table lists payments

1/1/2003
2/1/2003
3/1/2003

I am missing the payments for the following months:
11/1/2003
12/1/2003

These payments are not in the payment table, nor do we have the ability to load the month with a "0" payment.

Does anyone know how I can write logic to identify those months that are not in the data but fall in the month range of the coverage?

 

[Madawc]

There is a long-winded but reliable solution, do a set of totals in the Report Footer for everything that might exist, with Running Totals for that date range, which will be zero if that month has no entries.

It needn't be hard-code, you could generate them from the current date using commands like
Dateadd ("m", -12, currentdate)

Possibly something clever could be done using tables, but I've never tried using tables in Crystal.

Madawc Williams
East Anglia, Great Britain

 

[synapsevampire]

Use a loop, providing you have a recent version of Crystal.

I don't have Crystal loaded, but the code would look something like:

whileprintingrecords;
stringvar missingdates;
numbervar MonthNums := datediff("m",{table.start},{table.end});
numbervar Counter;
CurrMonthYear := {table.start};
For Counter := 1 to Monthnums do(
if isnull({table.payments.id}) then
missingdates := missingdates + totext(CurrMonthYear)+chr(13);
currmonthyear := dateadd("m",1,currmonthyear)
);
missingdates

Should get you close.

-k