Update previous records with yesterday's date

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
sam334
Premium Member
Premium Member
Posts: 145
Joined: Mon Aug 26, 2013 7:42 pm

Update previous records with yesterday's date

Post by sam334 »

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.
bharathappriyan
Participant
Posts: 47
Joined: Fri Sep 23, 2005 6:01 pm

Re: Update previous records with yesterday's date

Post by bharathappriyan »

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
sam334
Premium Member
Premium Member
Posts: 145
Joined: Mon Aug 26, 2013 7:42 pm

Post by sam334 »

Thanks @bharathappriyan. which functions should I use, like

I need something that will update previous row's end date to yesterday's date (from default date) if the employee change the department and new record comes from source data.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
sam334
Premium Member
Premium Member
Posts: 145
Joined: Mon Aug 26, 2013 7:42 pm

Post by sam334 »

Thanks for such a valuable information Craig and Ray. I can actually create another job to update the rows whenever there is a change record. But lookup is always better. Will let you know if I have any issue. For now workaround.....Thanks again.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

OK... just a couple of small points. There's absolutely no need for a second job. And I wouldn't consider anything I wrote up to be any kind of a 'workaround' but rather How It Is Done. FYI.
-craig

"You can never have too many knives" -- Logan Nine Fingers
sam334
Premium Member
Premium Member
Posts: 145
Joined: Mon Aug 26, 2013 7:42 pm

Post by sam334 »

Craig, Let me understand the design.

Currently, the design is
ODBC------
FTP Stage- Tranformer- ODBC

Reference odbc is same as target odbc where it updates old records and insert new records.

you want me to add one more link to update the records of lookup succeeds correct?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Correct. Two links from the Transformer to the target, one for inserts and one for updates.
-craig

"You can never have too many knives" -- Logan Nine Fingers
sam334
Premium Member
Premium Member
Posts: 145
Joined: Mon Aug 26, 2013 7:42 pm

Post by sam334 »

Okay. Thanks Craig. It worked. Awesome..
Post Reply