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
COMPUTE START_SAL/D12.2 = LST.SALARY;
COMPUTE END_SAL/D12.2 = FST.SALARY;
WF 8 version 8.2.04. Windows.
In focus since 1990.
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.
This should do it:
TABLE FILE ODOM
COUNTER/I5 = COUNTER + 1;
ON TABLE HOLD AS ODOMETER
-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;
TABLE FILE ODOMETER
ON TABLE HOLD AS FINAL
TABLE FILE FINAL
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
I think we went off the deep end with complicating this request. The following should work.
TABLE FILE xxx
SUM FST.LASTODOMREADING LST.Thisodomreading
When using an RDBMS as the data source, the rules change a bit for using FST. and LST. so verify that this gives you what you are looking for.
Maybe one of th FOCWizards could expound on the usage of FST. and LST. when reporting from an RDBMS. Is this documented anywhere?
Ginny, There is some information about the usage of FST. and LST. in the Interface Manual specific to your RDBMS. Basically it looks like it should work as advertised as long as the KEYS setting in the FOCUS Access File Descriptions for your SQL Server TABLES are set properly.
An explanation of FST. and LST. with regard to relational databases, is in the FOCUS for S/390 Relational Data Adapter User's Manual Version 7.2 Try page 4-28 (involving KEYORDER), and page 7-12, where it discusses translating FST. and LST. to SQL MIN and MAX.
Hope this helps.
One frivolous note: M's solution will only work if the lowest odometer reading is associated with the lowest date, and the highest reading is associated with the highest date. However, since your data appears to be so organized, it ought to work, barring any mistakes in data entry.
|Powered by Social Strata|