T SQL to track changes

Question

T SQL to track changes

snigdha.mishra190

Newbie

Points: 3
More actions
December 4, 2011 at 11:02 am

#391024

Hi,
I have a requirment which i have never worked on and need some help to proceed.
I have a list of cutomers with their effective date and exp date for a perticular service center. Cutomer can change the service center multiple times in a month and company needs a report on tracking these changes . Below is a sample data.
cutomerNumber Accounting Period Efective Date Expiration Date Service Center Status
CUST00101 5/15/2010 5/1/2010 NULL SSC1 New
CUST00101 6/15/2010 5/1/2010 NULL SSC1 Active
CUST00101 7/15/2010 5/1/2010 10/16/2010 SSC1 Active
CUST00101 8/15/2010 5/1/2010 10/16/2010 SSC1 Active
CUST00101 9/15/2010 5/1/2010 10/16/2010 SSC1 Active
CUST00101 10/15/2010 5/1/2010 10/16/2010 SSC1 Active
CUST00101 10/15/2010 10/20/2010 10/24/2010 SSC2 Change to SSC2
CUST00101 11/15/2010 10/25/2010 NULL SSC1 Change to SSC1
CUST00101 12/15/2010 10/25/2010 NULL SSC1 Active
Please help me out in this one.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

JonFox SSCrazy Points: 2157 More actions · Answer 1

It would be good if you could post an example of what the desired output should be. Also, if you could post your sample data as INSERT statements that would be very helpful as well. This article explains how to post sample data and DDL so that it is easier for us to help you: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

snigdha.mishra190 (12/4/2011)
Hi,
I have a requirment which i have never worked on and need some help to proceed.
I have a list of cutomers with their effective date and exp date for a perticular service center. Cutomer can change the service center multiple times in a month and company needs a report on tracking these changes . Below is a sample data.
cutomerNumber Accounting Period Efective Date Expiration Date Service Center Status
CUST00101 5/15/2010 5/1/2010 NULL SSC1 New
CUST00101 6/15/2010 5/1/2010 NULL SSC1 Active
CUST00101 7/15/2010 5/1/2010 10/16/2010 SSC1 Active
CUST00101 8/15/2010 5/1/2010 10/16/2010 SSC1 Active
CUST00101 9/15/2010 5/1/2010 10/16/2010 SSC1 Active
CUST00101 10/15/2010 5/1/2010 10/16/2010 SSC1 Active
CUST00101 10/15/2010 10/20/2010 10/24/2010 SSC2 Change to SSC2
CUST00101 11/15/2010 10/25/2010 NULL SSC1 Change to SSC1
CUST00101 12/15/2010 10/25/2010 NULL SSC1 Active
Please help me out in this one.
Thanks

First, read up on a thing called "Slowly Changing Dimensions" (or "SCDs" as they're sometimes called). Don't let the word "Dimensions" throw you... this doesn't have anything to do with "Data Warehousing" although the technique can be used there. Here's the link I recommend for a first into into SCDs.

http://en.wikipedia.org/wiki/Slowly_changing_dimension

For your particular problem, it would be called a "Type 2 SCD". This feat of SQL prestidigitation may be accomplished either by whatever code you're using to make the changes or by a trigger. I recommend the trigger because it's not really going to be any more of a burden on the system than doing it in code and the trigger has the hidden benefit of capturing even manual changes.

Read up on triggers in Books Online (the free help system that comes with SQL Server) so we can talk about how to implement this without you going "Huh? Wha?". 😉 Pay particular attention to what they say about the INSERTED and DELETED pseudo-tables of a trigger.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 3

how about using triggers and an audit table for insert update and delete. So every time a record is updated inserted or deleted yoit will be added to a log table.?

***The first step is always the hardest *******

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 4

Hi

Friend I need very much clear picture for what you actually want i am getting littel bit confuseing ....

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com