Thursday, 22 August 2013

Lost on creating a query for this scenario

Lost on creating a query for this scenario

Here is a simplified version of the table structure.
Employee
(
ID GUID NOT NULL
OldID GUID NULL
Name VARCHAR(50) NOT NULL
CreationDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
)
It contains employee information as well as any changes been made to
employee attributes. This way we can get a complete audit of changes been
made. When OldID is NULL, that basically mean the latest data. Here is an
example, I am using integer values for identifier to make this example
easier to understand.
ID OldId Name CreationDate
13 NULL John 15-July-2013
12 13 John1 14-July-2013
11 12 John2 13-July-2013
10 11 John3 12-July-2013
121 NULL Smith 15-July-2013
So there are two employees (as for these two cases OldId is null ) and
their latest names are john and Smith. I need to get the earliest name of
same employee and not sure how to query this data to get earliest name
also (John3) Any help will be highly appreciated.

No comments:

Post a Comment