Loop -SP Help? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Loop -SP Help?

Author

Topic

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-07-23 : 05:45:05

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-23 : 06:52:08

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

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-07-23 : 07:31:14

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-23 : 09:00:32

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

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-07-24 : 05:41:39

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-24 : 06:09:46

[code]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

[/code]

Madhivanan

Failing to plan is Planning to fail

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-07-24 : 08:27:54

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

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-07-25 : 10:38:54

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


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
--------------------------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources