SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: Join Query Help

  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2004
    Location
    ny
    Posts
    560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join Query Help

    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:
    Code:
    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
    Code:
    	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. #2
    SitePoint Evangelist
    Join Date
    Jun 2004
    Location
    ny
    Posts
    560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •