I have the following fields
DATE LASTODOMREADING Thisodomreading
6/28/2002 535 666
6/30/2002 666 767


If I do a report where I just want the LastOdomreading from the beginning of the date range and the THISodomreading from the ending of the date range how can I do this?

So the data to be returned should be
Date Range Lowest Odom HighestODOM
6/28 - 6/30 535 767

this sample is against the employee.foc db
see if this is what you want.

TABLE FILE EMPLOYEE
SUM
COMPUTE START_SAL/D12.2 = LST.SALARY;
COMPUTE END_SAL/D12.2 = FST.SALARY;
BY EMP_ID
END

Could you just use the prefix of MIN. and MAX. to get what you want?

min and max refer to values while fst and lst refer to physical instances.

Ginny,

This should do it:

TABLE FILE ODOM
PRINT
LASTREADING
THISREADING
COMPUTE
COUNTER/I5 = COUNTER + 1;
BY RDATE
ON TABLE HOLD AS ODOMETER
END
-RUN

-SET &NUMRECS = &LINES;

DEFINE FILE ODOMETER
FIRSTDATE/MDYY = IF COUNTER EQ 1 THEN RDATE ELSE FIRSTDATE;
LASTDATE/MDYY = IF COUNTER EQ &NUMRECS THEN RDATE ELSE FIRSTDATE;
LASTREAD/I7 = IF COUNTER EQ 1 THEN LASTREADING ELSE 0;
THISREAD/I7 = IF COUNTER EQ &NUMRECS THEN THISREADING ELSE 0;
END

TABLE FILE ODOMETER
PRINT
FIRSTDATE
LASTDATE
LASTREAD
THISREAD
ON TABLE HOLD AS FINAL
END

TABLE FILE FINAL
SUM
FIRSTDATE
LASTDATE
LASTREAD
THISREAD
END

In case it isn't clear: In that first step, ODOM is your source file and RDATE is your date field. It's a lot of steps, I know, but I can't think of a way to do it in fewer. If any of it is confusing, I would suggest taking the HOLD out of the first step to see the results, then putting it back and taking HOLD out of the second step.

Thanks, I appreciate the help. I have decided it's just easier to do all of the work through views on the SQL server and then just use Webfocus to pull the results.

You shoud be able to do a simple table request

  
TABLE FILE xxx
PRINT
MIN.LASTODOMREADING
MAX.Thisodomreading
AND COMPUTE ROWCNT/I9 = IF DATE EQ LAST DATE THEN LAST ROWCNT + 1 ELSE 1; NOPRINT
BY DATE
WHERE TOTAL ROWCNT EQ 1;
END