how to get the latest record from dimension table?

[karthikgk0705]

Hi all,
I have a scenario which goes like this.
i have a dimension table named 'customer' with columns customer id,customer name and address.
If a particular customer say A,keeps changing his address,how do i bring in those changes in my customer table.
Conditions are that all the previous addresses should also be preserved,eff date or time stamp shd not be used.And also when the end-user enquires about the customer A,he shd get the latest details about him.

Can anyone clarify me?

[rasi]

Hi Karthik

Please do a search on Slowly Changing Dimension Type 2 you will get heaps of results to answer your question

[ray.wurlod]

Create a query that constrains on the customer ID and the most recent (probably maximum) surrogate key value via a correlated subquery.

Code: Select all

SELECT T1.CUSTOMER_KEY 
FROM   CUSTOMER_DIM T1
WHERE  CUSTOMER_ID = 'A'
AND    T1.CUSTOMER_KEY = (SELECT MAX(T2.CUSTOMER_KEY)
                          FROM   CUSTOMER_DIM T2
                          WHERE  T1.CUSTOMER_ID = T2.CUSTOMER_ID);

[kumar_s]

If timestamp or data shouldnt be used, then to fetch the latest information, you need to have another column which represents the version of the data. Or as simply utilizing the surrogate key as Ray suggested, which will increase sequentially.