Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Identifying Changed Data

Status
Not open for further replies.

briangriffin

Programmer
Nov 6, 2008
878
US
I've started getting a flat file each day of all employees and the usual HR data - not a lot, about 700 employees and 20 fields. Every employee is in the file every day.

Right now I'm working on the ETL piece to get it all into a table, and I think I'll store all the data each day for auditing purposes. Ultimately I need to produce a file each day of any new employees (no problem there) but also any employee where any piece of data has changed from the previous day. I can pull everything out of the history table directly into the report, or I could load those changed records into another table to make the reporting piece a snap.

This is a classic type of request so before I start lumbering through it I thought I'd get some advice on the best approach from people who have done it before. Thanks in advance.
 
The article mentions storing a checksum value with the data. While the article also shows the checksum equality of differeing words, the whole record checksum will most seldom be identical on a changed employee record.

Another thing you can put into any record to detect any change is a timestamp, they automatically allter with any change. So when you join yeaterdays data with todays on ID and find different timestamp you know something has changed in that ID.

Even better, when you query any data with timestamps larger than the highest timestamp of yesterday you get all changes since yesterday including new entries.

Bye, Olaf.
 
You can compare yesterday's and today's record in the flat file (before you import it). If the employee number is the first field, you can use that as sort of a key for the two records. If it's a fixed-length file, all the better; just compare using SUBSTRING. Otherwise, you'd have to parse the record to get the employee number for matching.



-- Francis
Francisus ego, sed non sum papa.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top