Join Query Help

Databases
1

Hi all, the below query outputs an historical data for all columns in unsaccountB tbl. I need to add the current value (whatever is stored on outputed columns on unsaccountB tbl) of all outputed columns (dbc.ColumnName). How would I do that? Do you need any other info to help me with this thread?

current sample output:



firstname	lastname	XProxyContext	OldValue	operationDate	OperationType	ColumnName	TableName
Abdulhalik	Sentürk	ORACLE-FSTEST	sa	2012-04-27 13:55:32.727	U	XUserUpdated	UNSAccountB
Abdulhalik	Sentürk	ORACLE-FSTEST	sa	2012-04-27 13:55:32.617	U	XUserUpdated	UNSAccountB
Abdulkadir	Arslan	BOSS	U	2012-04-02 12:07:45.170	U	TSTIDM_Consistent	UNSAccountB
Abdulkadir	Arslan	BOSS	2011-07-29 16:34:51.357	2012-04-02 12:07:45.170	U	XDateUpdated	UNSAccountB
Abdulkadir	Vural	CARDIX	0	2012-05-30 11:20:33.663	U	AccountDisabled	UNSAccountB
Abdulkadir	Vural	CARDIX	1	2012-05-30 11:19:34.143	U	AccountDisabled	UNSAccountB
Abdulkadir	Vural	CARDIX	0	2012-05-18 07:46:01.783	U	AccountDisabled	UNSAccountB
Abdulkadir	Vural	CARDIX	1	2012-05-18 07:45:48.027	U	AccountDisabled	UNSAccountB
Abdulkadir	Vural	CARDIX	0	2012-05-09 12:40:41.640	U	AccountDisabled	UNSAccountB
Abdulkadir	Vural	CARDIX	1	2012-05-09 12:40:11.333	U	AccountDisabled	UNSAccountB
Abdulkadir	Vural	CARDIX	0	2012-04-11 09:38:38.850	U	AccountDisabled	UNSAccountB


	SELECT 	p.firstname,p.lastname, u.XProxyContext, dw.[OldValue],
		dwo.operationDate, dwo.OperationType,dbc.ColumnName,dbc.TableName
	FROM DialogWatchProperty dw
		INNER JOIN DialogWatchOperation dwo ON dw.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation
		INNER JOIN UNSAccountB u ON dwo.ObjectKey = u.xObjectKey
		INNER JOIN Person p ON p.uid_person = u.uid_person
		INNER JOIN DialogColumn dbc on  dbc.UID_DialogColumn = dw.UID_DialogColumn
	where ---OperationType = 'U'
		dwo.operationDate between convert(varchar,@sDate,1) and convert(varchar,@eDate,1)
		And p.FirstName = (case when @FirstName <>'' then @FirstName else p.FirstName end)
		And p.LastName = (case when @LastName <>'' then @LastName else p.LastName end)
		And p.CentralAccount =
		(case when @userName <>'' then @userName else p.CentralAccount end)
		And dwo.ObjectKey in ( select XObjectKey
		from
		UNSAccountB
		where UID_Person in ( select UID_Person
		from Person
		where PersonnelNumber = (case when @personelNumber <>'' then @personelNumber else PersonnelNumber end) ) )
		
	order by firstname,lastname,dbc.ColumnName asc, dwo.operationDate desc
2

Basically there should be another column “CurrentValue” and should be output the current value of outputed table column…UnsAccountB table columns are the same as DialogColumn tbl columnName field. Basically the current data is stored on unsaccountB tbl. DialogWatchPropery and DialogWatchOperation contain historical data and dialogColumn contains db tables structure information.

sample:
columnName CurrentValue oldValue
AccountDisabled F T
AccountDisabled T T