isolating weekly changes to a table

Question

isolating weekly changes to a table

justintime

Ten Centuries

Points: 1060
More actions
March 2, 2010 at 9:11 am

#137451

hello all. i have an employee table and i would like to know which records changed on a weekly basis. for example i would like to know when a person changes departments or locations (building,floor,room).
ideally i would compare the current table against the previous table and export the changes to a third table or an excel file. i have an employee ID which is my PK.
i have played around with the SCD transform in SSIS but it appears to be overkill and is difficult for a semi-newbie to conquer. i am not concerned with overwriting any records SCD 1 or creating any SCD 2 type records.
what would be the best way to go about this?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

You have a single table containing all that stuff? Sounds like it might be in need of some normalisation ...

Do you want to track the change dates separately for each of the fields on the table, or is a single 'Record Last Modified' field enough?

Are you hoping to have a series of weekly tables building up over time, or just a snapshot of what has changed in the previous week at any one time? Have you considered just using a view if this is the case?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

justintime Ten Centuries Points: 1060 More actions · Answer 2

Hello again Phil.

You have a single table containing all that stuff? Sounds like it might be in need of some normalisation ...

this particular table does not come from the live database but is a data dump that i keep locally and joins data from a few different tables. the database itself is normalized. there are about 20,000 records in this table

Do you want to track the change dates separately for each of the fields on the table, or is a single 'Record Last Modified' field enough?

ideally i would like to know which fields changed and on which date but it sounds like a 'Record Last Modified' would be enough for the immediate need as long as it tells me what the change was. for example i need to know that em# 1025874 changed from IT department to Finance. i also need to know if that person changed from building A to building B or room C to room D.

Are you hoping to have a series of weekly tables building up over time, or just a snapshot of what has changed in the previous week at any one time? Have you considered just using a view if this is the case?

for now i would be fine knowing only what has changed since the previous week. at that point my current would become the previous and the current data would be deleted, then populated at the beginning of the next week.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

That is a fairly horrendous requirement 🙂

Unless you can put logic into the front-end application to track Field Last Modified, you just won't have that info. Same with 'Record Last Modified'.

Let's assume you have two tables:

Emp(ID, F1, F2)

and

Emp_Hist(ID, F1, F1_Last_Modified, F2, F2_Last_Modified)

Emp_Hist is obviously your change-tracking table.

INSERTing new records is straightforward. But modifications ... nasty. You might have to work one field at a time to get any sort of semi-accurate date modified data:

UPDATE eh

Set F1 = e.F1, F1_Last_Modified = GetDate()

FROM EmpHist eh

Join Emp e on eh.ID = e.ID

Where Coalesce(eh.F1, '') <> Coalesce(e.F1, '')

Coalesce used to get round issues comparing NULLs.

etc etc for the other fields.

As I mentioned above, if you have any Date Modified fields in your source data, they can be used to help identify the updates.

You might also find the CHECKSUM function of use in creating your UPDATE queries.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

JustMarie SSCertifiable Points: 7771 More actions · Answer 4

I did this using the brute force method of multiple tables.

1. Copy existing table into 'new' table (yesterday's data)

2. Update the main table (today's data)

3. Run T-SQL to compare the two on each of the fields where I wanted to find changes and put into an audit table.

I found this to be pretty darn useful and hard to break. Yes, if I needed to find changes on a new field I'd have to add it to the code but it was an easy solution to the problem.

justintime Ten Centuries Points: 1060 More actions · Answer 5

gotta give thanks to both of you. i used the brute force method, combined with COALESCE command since i'm comparing NULLs.

thanks!

Phil Parkin SSC Guru Points: 246962 More actions · Answer 6

Thanks for posting back & glad to hear of the happy ending 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin