Update previous records with yesterday's date
Moderators: chulett, rschirm, roy
Update previous records with yesterday's date
All,
Need a help on updating previous records with yesterdays date.
We have table Employee detail, where we have
employeeid, department, start_date, end_date.
Sam, IT, 2014-07-25, 12/31/9999
Now, when the employee change the department, it should update the end_date to yesterdays date and insert start_date as today's date.
employeeid, department, start_date, end_date.
Sam, IT, 2014-07-25, 2014-07-27
Sam, IT Assurance,2014-07-28, 12/31/9999
Any lead how to achieve it in server job. adding start date as today's date and end date as today's date - 1 won't work as it will update the current record instead of previous record.
Thanks.
Need a help on updating previous records with yesterdays date.
We have table Employee detail, where we have
employeeid, department, start_date, end_date.
Sam, IT, 2014-07-25, 12/31/9999
Now, when the employee change the department, it should update the end_date to yesterdays date and insert start_date as today's date.
employeeid, department, start_date, end_date.
Sam, IT, 2014-07-25, 2014-07-27
Sam, IT Assurance,2014-07-28, 12/31/9999
Any lead how to achieve it in server job. adding start date as today's date and end date as today's date - 1 won't work as it will update the current record instead of previous record.
Thanks.
-
- Participant
- Posts: 47
- Joined: Fri Sep 23, 2005 6:01 pm
Re: Update previous records with yesterday's date
Hi,
Sort the data based on start_dt desc. Assign yesterday's date as Start date and the High end date as end date for employee's first record. For the second record, use (Previous record's start date -1 ) as end date.
Thanks,
Bharathappriyan
Sort the data based on start_dt desc. Assign yesterday's date as Start date and the High end date as end date for employee's first record. For the second record, use (Previous record's start date -1 ) as end date.
Thanks,
Bharathappriyan
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Today's date is given by Date() therefore yesterday's date is given by Date() - 1 since dates are stored internally as integer offsets from a known day zero.
Initialize two stage variables with these values so that they only need to be calculated once.
If needed, use Oconv() functions to convert to the required string format.
Initialize two stage variables with these values so that they only need to be calculated once.
If needed, use Oconv() functions to convert to the required string format.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
So you're just asking how to do a Type 2 change? Meaning, a new record is inserted for the effective date of the change and the original / old record is closed / marked as no longer current in some fashion.
You must be checking for existence already so you know whether this is a new or changed record. When you don't get a hit on the lookup you are inserting a new record using the current date and that '12/31/9999' end date, correct? Well, you would always do that step regardless of the lookup success or failure. A change just adds one more link to the target: the update link where whatever "primary key" identifies the old row (that you captured from the lookup) is used to update the END_DATE field of that record to your "current date -1" value. Constrain that link to only fire if the lookup succeeds.
Note that this assumes a record is always either new or a change and that is why you are processing it. There is a little bit more to it if you may be getting unchanged data and need to check to see if anything changed.
Oh, and since a record can go through multiple changes, make sure your existence lookup only queries for the 'most current' version - i.e. in your case that looks like the record with the 12/31/9999 date.
You must be checking for existence already so you know whether this is a new or changed record. When you don't get a hit on the lookup you are inserting a new record using the current date and that '12/31/9999' end date, correct? Well, you would always do that step regardless of the lookup success or failure. A change just adds one more link to the target: the update link where whatever "primary key" identifies the old row (that you captured from the lookup) is used to update the END_DATE field of that record to your "current date -1" value. Constrain that link to only fire if the lookup succeeds.
Note that this assumes a record is always either new or a change and that is why you are processing it. There is a little bit more to it if you may be getting unchanged data and need to check to see if anything changed.
Oh, and since a record can go through multiple changes, make sure your existence lookup only queries for the 'most current' version - i.e. in your case that looks like the record with the 12/31/9999 date.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers