DECLARE @TargetSystem nvarchar(35) = 'ads'
DECLARE @sDate datetime ='2012-01-01'
DECLARE @eDate datetime ='2012-07-31'
DECLARE @personelNumber nvarchar(32)  = '89722'
DECLARE @firstName nvarchar(32)  = ''
DECLARE @lastName nvarchar(32) = ''
DECLARE @userName nvarchar(32) = ''
DECLARE @accountType tinyint = '1' --- 0/1 { 0=SecAcc;1=PrimAcc }


;with cte (sno,PersonnelNumber, OldValue,operationDate )
as
(
	select ----firstname,lastname,
	row_number() over (partition by PersonnelNumber order by dwo.operationDate desc) as sno,
	PersonnelNumber, OldValue,operationDate 
	from DialogWatchProperty  dw, Person p, DialogWatchOperation dwo,DialogColumn dbc
	where 
			dbc.UID_DialogColumn ='8FCEF7B6-674E-4FFB-A9A1-AA76CED26E30'
			and dw.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation
			And dbc.UID_DialogColumn = dw.UID_DialogColumn
			And dwo.ObjectKey = p.xObjectKey
			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 PersonnelNumber = (case when @personelNumber <>'' then @personelNumber else PersonnelNumber end)
	--order by firstname,lastname
)

select t1.* from
(

	SELECT 	
		p.firstname,p.lastname,p.PersonnelNumber,SystemName ='ADS',Entitlement='', dw.[OldValue],newVal ='',
		dwo.operationDate, dwo.OperationType,dbc.ColumnName,dbc.TableName
	FROM DialogWatchProperty dw
		INNER JOIN DialogWatchOperation dwo ON dw.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation
		INNER JOIN ADSAccount a ON dwo.ObjectKey = a.xObjectKey
		INNER JOIN Person p ON p.uid_person = a.uid_person
		INNER JOIN DialogColumn dbc on  dbc.UID_DialogColumn = dw.UID_DialogColumn
	where
			p.FirstName = (case when @FirstName <>'' then @FirstName else p.FirstName end)
			And p.LastName = (case when @LastName <>'' then @LastName else p.LastName end)
			And PersonnelNumber = (case when @personelNumber <>'' then @personelNumber else PersonnelNumber end)
	union all	
	
		SELECT 	
			p.firstname,p.lastname,p.PersonnelNumber,SystemName ='ADS',ag.DisplayName as Entitlement, dw.[OldValue],newVal ='',
			dwo.operationDate, dwo.OperationType,dbc.ColumnName,dbc.TableName
		FROM DialogWatchProperty dw
			INNER JOIN DialogWatchOperation dwo ON dw.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation
			INNER JOIN ADSAccountInADSGroupTotal at ON dwo.ObjectKey = at.xObjectKey
			INNER JOIN ADSGroup ag on at.UID_ADSGroup = ag.UID_ADSGroup
			INNER JOIN ADSAccount a on a.UID_ADSAccount = at.UID_ADSAccount		
			INNER JOIN Person p ON p.uid_person = a.uid_person
			INNER JOIN DialogColumn dbc on  dbc.UID_DialogColumn = dw.UID_DialogColumn
		where
			p.FirstName = (case when @FirstName <>'' then @FirstName else p.FirstName end)
			And p.LastName = (case when @LastName <>'' then @LastName else p.LastName end)
			And PersonnelNumber = (case when @personelNumber <>'' then @personelNumber else PersonnelNumber end)				
		
	union all
		SELECT p.firstname,p.lastname,p.PersonnelNumber, u.XProxyContext as SystemName,Entitlement='', dw.[OldValue],newVal =''
		,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
			p.FirstName = (case when @FirstName <>'' then @FirstName else p.FirstName end)
			And p.LastName = (case when @LastName <>'' then @LastName else p.LastName end)
			And PersonnelNumber = (case when @personelNumber <>'' then @personelNumber else PersonnelNumber end)		
	union all
		SELECT p.firstname,p.lastname,p.PersonnelNumber, u.XProxyContext as SystemName,ug.DisplayName as Entitlement, dw.[OldValue],newVal =''
			,dwo.operationDate, dwo.OperationType,dbc.ColumnName,dbc.TableName
		FROM DialogWatchProperty dw
			INNER JOIN DialogWatchOperation dwo ON dw.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation
			INNER JOIN UNSAccountBInUNSGroupBTotal ut ON dwo.ObjectKey = ut.xObjectKey
			INNER JOIN UNSGroupB ug on ut.UID_UNSGroupB = ug.UID_UNSGroupB
			INNER JOIN UNSAccountB u on u.UID_UNSAccountB = ut.UID_UNSAccountB
			INNER JOIN Person p ON p.uid_person = u.uid_person
			INNER JOIN DialogColumn dbc on  dbc.UID_DialogColumn = dw.UID_DialogColumn	
		where
			p.FirstName = (case when @FirstName <>'' then @FirstName else p.FirstName end)
			And p.LastName = (case when @LastName <>'' then @LastName else p.LastName end)
			And PersonnelNumber = (case when @personelNumber <>'' then @personelNumber else PersonnelNumber end)			

)
as t1 inner join
(
	select t1.PersonnelNumber, t1.operationDate as to_date,t2.operationDate as from_date 
	from cte as t1 inner join cte as t2 on t1.PersonnelNumber=t2.PersonnelNumber
	and t1.sno = t2.sno-1 where t1.Oldvalue=1 and t2.oldvalue=0
) as t2
on t1.PersonnelNumber=t2.PersonnelNumber

where  t1.operationDate >= t2.from_date and operationDate <= t2.to_date

order by t1.FirstName,t1.LastName,t1.OperationDate desc

AND @TargetSystem = 'ADS' 

AND @TargetSystem <> 'ADS' 

quote:

---

Originally posted by LoztInSpace

Put your condition in the WHERE clause as appropriate:
...

AND @TargetSystem = 'ADS' 

or

AND @TargetSystem <> 'ADS' 

You'll probably get a better job out of the query optimiser/processor if you write two queries though.

---

quote:

---

Originally posted by emmim44

quote:

---

Originally posted by LoztInSpace

Put your condition in the WHERE clause as appropriate:
...

AND @TargetSystem = 'ADS' 

or

AND @TargetSystem <> 'ADS' 

You'll probably get a better job out of the query optimiser/processor if you write two queries though.

---

okay, assume "@TargetSystem = '' " ...how would manage your where clause then?

---