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?This message has been edited. Last edited by: <Mabel>,
August 26, 2004, 05:26 PM
susannah
George, if you sort your db by date first, then the sum function will bring you the proper values across the board. or even the LST.EFFDT LST.CITY etc if you prefer, makes your code more readable.
August 27, 2004, 03:42 PM
EricH
George,
The short answer is that WebFOCUS for PeopleSoft (WFPS) provides techniques for handling PS effective dated tables. If you do not already have WFPS, I recommend that you contact your local IBI rep; and they will put you in touch with the folks who can give you more details.
EricH
August 30, 2004, 05:16 AM
Piipster
Try using a PRINT and
BY EMPLOYEE_ID BY HIGHEST 1 EFFDT
This will sort them in descending order by date and limit it to the first occurrence in the group.
Be sure to include WHERE statements that will return only valid active rows (ie. make sure they have the right status and aren't future dated).
December 05, 2005, 12:46 PM
<Mabel>
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?
December 05, 2005, 01:25 PM
fthompson
George,
The code I use for finding the most recent record is as follows(I'll use your field names):
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
See if that'll work for you. It works on our system but it's not peoplesoft.
**EDIT Forgot the by phrase. FletcherThis message has been edited. Last edited by: fthompson,