If you ran this code,

TABLE FILE EMPLOYEE
SUM CURR_SAL
BY DEPARTMENT
BY HIRE_DATE
END

You get:

DEPARTMENT HIRE_DATE CURR_SAL 
           06/04/12 $11,000.00 
MIS        81/07/01 $31,680.00 
           81/11/02 $27,062.00 
           82/04/01 $30,780.00   
           82/05/01 $18,480.00 
 PRODUCTION 82/01/04 $36,362.00 
             82/02/02 $16,100.00 
             82/07/01 $21,120.00 
             82/08/01 $29,700.00 

I'd like to modify the query to get the rows with the highest hire_date that has a total curr_sal that is GT $30,0000 by department.
You should then only return rows 4 and 6.

Is it possible to do this in one pass at the data using a combination of WHERE TOTAL and BY TOTAL?

Thanks,
Bethany

WF 7.1.3
DevStudio 7.1.4

This message has been edited. Last edited by: Bethany,

I think this would work.

TABLE FILE EMPLOYEE
SUM CURR_SAL
BY DEPARTMENT
BY highest 1 HIRE_DATE
if total curr_sal gt 30000
END

Good luck

ET

Thanks for the suggestion ET, but that code returns no records.

You wouldn't even need the "IF TOTAL". Just an "IF" would work.

quote:

TABLE FILE EMPLOYEE
SUM CURR_SAL
BY DEPARTMENT
BY highest 1 HIRE_DATE
if total curr_sal gt 30000
END

If you take out the if total, you will see there are no records greater than 30000. Try 20000.

What if there were 2 rows that came out for the highest date and one of the rows came out less than 30000 while the other row was greater ? Conversely what if the highest 1 date row had an amount less than 30000? I still think the if total is needed though maybe not in this example.

good luck

ET

When you sum by department and hire_date there are 3 records with totals higher than 30,000-2 for the MIS dept and 1 for PRODUCTION.

Using just IF CURR_SAL GT 30000, also returns no records.

DEPARTMENT HIRE_DATE CURR_SAL 
           06/04/12  $11,000.00 
MIS        81/07/01  $31,680.00 
           81/11/02  $27,062.00 
           82/04/01  $30,780.00 
           82/05/01  $18,480.00 
PRODUCTION 82/01/04  $36,362.00 
           82/02/02  $16,100.00 
           82/07/01  $21,120.00 
           82/08/01  $29,700.00

Interesting, BY HIGHEST 1 will return no values because it appears that WHERE TOTAL is filtering on each of the rows instead of the total by department. It looks to me that you need to do this in two passes.

TABLE FILE EMPLOYEE
SUM 
CURR_SAL
BY DEPARTMENT
BY HIGHEST HIRE_DATE
WHERE TOTAL CURR_SAL GT 30000
ON TABLE HOLD
END

TABLE FILE HOLD
SUM 
CURR_SAL
BY DEPARTMENT
BY HIGHEST 1 HIRE_DATE
END

Bethany,

Try:

TABLE FILE EMPLOYEE
SUM CURR_SAL
BY DEPARTMENT
BY HIGHEST 1 HIRE_DATE
WHERE TOTAL TOT.CURR_SAL GT 30000;
END

Ignore, this must be a load of cods!

This message has been edited. Last edited by: Alan B,

The test was and if total test (if TOTAL curr_sal gt tnat 30000) and not just an if test.

Okay, after last flop, try:

TABLE FILE EMPLOYEE
SUM CURR_SAL
COMPUTE
XSAL/I1=IF CURR_SAL GT 30000 THEN 1 ELSE 2;
COMPUTE
XDAT/I6YMD=IF DEPARTMENT NE LAST DEPARTMENT AND XSAL EQ 1 THEN HIRE_DATE ELSE
           IF DEPARTMENT NE LAST DEPARTMENT AND XSAL EQ 2 THEN 0 ELSE
           IF XSAL EQ 1 AND DEPARTMENT EQ LAST DEPARTMENT AND LAST XDAT GT HIRE_DATE THEN LAST XDAT ELSE
           IF XSAL EQ 1 AND DEPARTMENT EQ LAST DEPARTMENT AND LAST XDAT LT HIRE_DATE THEN HIRE_DATE ELSE
           LAST XDAT;
BY DEPARTMENT
BY HIRE_DATE
WHERE TOTAL XSAL EQ 1;
WHERE TOTAL HIRE_DATE EQ XDAT;
END

And 'cos it's late you should be able to improve my COMPUTE!

Looks to me like Alan is almost there. Ran his code and got:

PAGE 1

DEPARTMENT HIRE_DATE CURR_SAL XSAL XDAT
MIS 81/07/01 $31,680.00 1 81/07/01
82/04/01 $30,780.00 1 82/04/01
PRODUCTION 82/01/04 $36,362.00 1 82/01/04

Too many rows. But change BY HIRE_DATE to BY HIGHEST HIRE_DATE and you get the results you're looking for. Which I know Alan would have figured out in about two minnutes.

DWF

Thank you, you are correct. I missed the HIGHEST transposing from one machine to another!
Should be:

TABLE FILE EMPLOYEE
SUM CURR_SAL
COMPUTE
XSAL/I1=IF CURR_SAL GT 30000 THEN 1 ELSE 2;
COMPUTE
XDAT/I6YMD=IF DEPARTMENT NE LAST DEPARTMENT AND XSAL EQ 1 THEN HIRE_DATE ELSE
           IF DEPARTMENT NE LAST DEPARTMENT AND XSAL EQ 2 THEN 0 ELSE
           IF XSAL EQ 1 AND DEPARTMENT EQ LAST DEPARTMENT AND LAST XDAT GT HIRE_DATE THEN LAST XDAT ELSE
           IF XSAL EQ 1 AND DEPARTMENT EQ LAST DEPARTMENT AND LAST XDAT LT HIRE_DATE THEN HIRE_DATE ELSE
           LAST XDAT;
BY DEPARTMENT
BY HIGHEST HIRE_DATE
WHERE TOTAL XSAL EQ 1;
WHERE TOTAL HIRE_DATE EQ XDAT;
END

Hi everybody,

Try this:

TABLE FILE EMPLOYEE
SUM CURR_SAL
BY DEPARTMENT
BY TOTAL HIGHEST 1 HIRE_DATE
BY HIGHEST HIRE_DATE
WHERE TOTAL CURR_SAL GT 30000
END

With this I got the expected result.

Roland, you are right. Perfect, well done.

Don't know why I could not get that to work yesterday, I was obviously wrong.

(Ah,I see why, I got hung up on TOTAL TOT.CURR_SAL!)

Thanks to everyone for you help!

Bethany

quote:

BY TOTAL HIGHEST 1 HIRE_DATE

Roland,

That's something I've not done before! Thanks for letting this out of the bag!

Cheers,

Francis.

Everybody welcome.

It's the process WF goes through in preparing a report.
Once all the data are in the internal matrix, WF is doing (and in this order):

• COMPUTEs
  
• WHERE/IF TOTAL
  
• BY TOTAL
  

So if you are looking at the code and going through these steps, you can see.