DSXchange

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.

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

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.

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.

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.

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.

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, 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?

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

Okay. Thanks Craig. It worked. Awesome..