Need to Begin_Date and End_Date columns to an EFFDT table.

[rsadasivam]

Hi,

Is anyone know of an easy method of populating Begin and Date columns on a Peoplesoft EFFDT based table.

Eg:
From:
DEPTID EFFDT ......
12345 1/1/1900
12345 1/1/2000
12345 2/1/2000
12345 5/1/2000
12345 10/1/2000

TO:
DEPTID EFFDT ...... BEGIN_DT END_DT
12345 1/1/1900 1/1/1900 12/31/1999
12345 1/1/2000 1/1/2000 1/31/2000
12345 2/1/2000 2/1/2000 4/30/2000
12345 5/1/2000 5/1/2000 9/30/2000
12345 10/1/2000 10/1/2000 12/31/9999

Any assistance is appreciated.

Thanks in advance

 

[notadba]

What are you trying to do this in - a report, query, building a new table? Is it a one-off?

 

[jmanj]

It is hard to give assistance when you don't give enough information to work with. You have not responded to a simple questions to clarify your purpose, I can only assume you have no idea what PeopleSoft is all about. Here's some tips that you can ponder:

1. For reports or query you use either Crystal reporting or SQR programming tool.
2. If you are creating or modifying the structure of a table then you must know PeopleTools, data dictionaries and the reorganiztion of the database.
3. If you are asking for some date calculation then there are many ways to do it: Build an api in java, javascript or perl to do date calculation. Using SQR you can convert the dates into century dates and manipulate from there.
4. If you are populating an existing field then use SQR.

My real question here is why do you want to create an end-date? In PeopleSoft EFFDT marks the beginning of a record and the end of another. Maybe are you asking how to get the record(s) with a max or min EFFDT?