quote:

Originally posted by George Brown:
Is anyone out there using WebFOCUS with peoplesoft or with effective dates?

I want to know how to handle effective date logic in a report. I can do a sum and take the max but only that data element is the correct one while the rest are the last record in the database matching the other criteria.

Basically I want the below data to show all of the current addresses for each employee ID.

I tried

TABLE FILE SAMPLE_DATES
SUM
EFFDT AS 'MAX,EFFDT'
CITY
STATE
EFF_STATUS
BY
EMPLID
SAMPLE_DATES data...
EMPLID EFFDT CITY STATE EFF_STATUS
00000000001 1/1/2000 Elmhurst IL A
00000000001 1/1/1900 Chicago IL I
00000000002 1/1/1900 St. Louis MO I
00000000002 1/1/2000 Madison WI A
00000000003 1/1/1900 Seatle WA I
00000000003 1/1/2000 Portland OR A
Should return


EMPLID EFFDT CITY STATE EFF_STATUS
00000000001 1/1/2000 Elmhurst IL A
00000000002 1/1/2000 Madison WI A
00000000003 1/1/2000 Portland OR A

Instead it returns


EMPLID EFFDT CITY STATE EFF_STATUS
00000000001 1/1/2000 Chicago IL I
00000000002 1/1/2000 Madison WI A
00000000003 1/1/2000 Portland OR A
Note that the effective dates are all correct but the other data is not from the same row as the max effdt. How would I do this?

In this particular case I could use EFF_STATUS but this isn't always an option. Can anyone help me?

TABLE FILE SAMPLE_DATES
SUM
MAX.EFF_DT
BY EMPLID
PRINT CITY STATE EFF_STATUS
BY EMPLID
WHERE TOTAL EFF_DT EQ MAX.EFF_DT
END