SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Loop -SP Help?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

emmim44
Yak Posting Veteran

65 Posts

Posted - 07/23/2012 :  05:45:05  Show Profile  Reply with Quote
Hi all,
I am trying to succeed:
1)Loop through this samle output (that could be lots of records)
2)If same personnelnumber and current row's oldValue is 1 and previous row's value is 0
3)Take current and previous operationDate and select some records from another tbl between those operationDates. Each iteration will append the result of select statement and populate the final output.


PersonnelNumber	OldValue	operationDate
89722	1	2012-04-19 21:00:24.150
89722	0	2012-04-17 15:09:33.720
89722	1	2012-04-17 14:46:06.410
89722	0	2012-04-17 12:03:54.313
89783	1	2012-05-06 21:00:33.917
89783	0	2012-05-04 12:14:32.077
89778	1	2012-05-06 21:00:33.913
89778	0	2012-05-04 12:14:32.087

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/23/2012 :  06:52:08  Show Profile  Reply with Quote
Hard to say what the query should be without seeing the DDL for the tables, some sample data along with the output.

Just from reading your posting, it seems like you wouldn't need to do a looping operation (which is inefficient in SQL).

If you need help posting DDL etc., take a look at Brett's blog - good guidance in there: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

emmim44
Yak Posting Veteran

65 Posts

Posted - 07/23/2012 :  07:31:14  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck

Hard to say what the query should be without seeing the DDL for the tables, some sample data along with the output.

Just from reading your posting, it seems like you wouldn't need to do a looping operation (which is inefficient in SQL).

If you need help posting DDL etc., take a look at Brett's blog - good guidance in there: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx



I need some sample template to proceed. I do not need post DDL things.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 07/23/2012 :  09:00:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Start with this



with cte (sno,PersonnelNumber, OldValue,operationDate )
as
(
select row_number() over (partition by PersonnelNumber order by PersonnelNumber) as sno,
PersonnelNumber, OldValue,operationDate
from table
)

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


Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 07/23/2012 09:01:10
Go to Top of Page

emmim44
Yak Posting Veteran

65 Posts

Posted - 07/24/2012 :  05:41:39  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

Start with this



with cte (sno,PersonnelNumber, OldValue,operationDate )
as
(
select row_number() over (partition by PersonnelNumber order by PersonnelNumber) as sno,
PersonnelNumber, OldValue,operationDate
from table
)

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


Madhivanan

Failing to plan is Planning to fail


I think that you got my point... I had adjusted the query as follow but getting syntax errors...

Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'as'.


with cte 
as
(
	select 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

)

(
	SELECT 	
		p.firstname,p.lastname,p.PersonnelNumber, 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
		)


as t1 inner join
(
select t1.PersonnelNumber, t1.operationDate as from_date,t2.operationDate as to_date 
from cte as t1 inner join cte as t2 on t1.PersonnelNumber=t2.PersonnelNumber
and t1.sno+1=t2.sno where t1.Oldvalue=0 and t2.oldvalue=1
) as t2
on t1.PersonnelNumber=t2.PersonnelNumber
where  date_col >= t2.from_date and date_col <= t2.to_date
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 07/24/2012 :  06:09:46  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
with cte (sno,PersonnelNumber, OldValue,operationDate )
as
(
	select 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

)

select t1.* from
(
	SELECT 	
		p.firstname,p.lastname,p.PersonnelNumber, 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
		)


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



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

emmim44
Yak Posting Veteran

65 Posts

Posted - 07/24/2012 :  08:27:54  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

with cte (sno,PersonnelNumber, OldValue,operationDate )
as
(
	select 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

)

select t1.* from
(
	SELECT 	
		p.firstname,p.lastname,p.PersonnelNumber, 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
		)


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



Madhivanan

Failing to plan is Planning to fail


There are 153 records delegated. And the below query return zero and when I comment out "where operationDate >= t2.from_date and operationDate <= t2.to_date" it returns some records but some of the delegated employees are missing...Condition might not be correct.


with cte (sno,PersonnelNumber, OldValue,operationDate )
as
(
	select 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
		---order by firstname,lastname
)

select t1.* from
(
	SELECT 	
		p.firstname,p.lastname,p.PersonnelNumber,SystemName ='ADS', 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
	union all
		SELECT p.firstname,p.lastname,p.PersonnelNumber, u.XProxyContext as SystemName, 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
		)


as t1 inner join
(
	select t1.PersonnelNumber, t1.operationDate as from_date,t2.operationDate as to_date 
	from cte as t1 inner join cte as t2 on t1.PersonnelNumber=t2.PersonnelNumber
	and t1.sno+1=t2.sno where t1.Oldvalue=0 and t2.oldvalue=1
) as t2
on t1.PersonnelNumber=t2.PersonnelNumber
----order by t1.FirstName,t1.LastName
where  operationDate >= t2.from_date and operationDate <= t2.to_date


Go to Top of Page

emmim44
Yak Posting Veteran

65 Posts

Posted - 07/25/2012 :  10:38:54  Show Profile  Reply with Quote
quote:
Originally posted by emmim44

quote:
Originally posted by madhivanan

with cte (sno,PersonnelNumber, OldValue,operationDate )
as
(
	select 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

)

select t1.* from
(
	SELECT 	
		p.firstname,p.lastname,p.PersonnelNumber, 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
		)


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



Madhivanan

Failing to plan is Planning to fail


There are 153 records delegated. And the below query return zero and when I comment out "where operationDate >= t2.from_date and operationDate <= t2.to_date" it returns some records but some of the delegated employees are missing...Condition might not be correct.


with cte (sno,PersonnelNumber, OldValue,operationDate )
as
(
	select 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
		---order by firstname,lastname
)

select t1.* from
(
	SELECT 	
		p.firstname,p.lastname,p.PersonnelNumber,SystemName ='ADS', 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
	union all
		SELECT p.firstname,p.lastname,p.PersonnelNumber, u.XProxyContext as SystemName, 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
		)


as t1 inner join
(
	select t1.PersonnelNumber, t1.operationDate as from_date,t2.operationDate as to_date 
	from cte as t1 inner join cte as t2 on t1.PersonnelNumber=t2.PersonnelNumber
	and t1.sno+1=t2.sno where t1.Oldvalue=0 and t2.oldvalue=1
) as t2
on t1.PersonnelNumber=t2.PersonnelNumber
----order by t1.FirstName,t1.LastName
where  operationDate >= t2.from_date and operationDate <= t2.to_date






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
--------------------------------------------------------------------------------
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000