| Author |
 Topic  |
|
|
emmim44
Yak Posting Veteran
65 Posts |
 Posted - 07/23/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/23/2012 : 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 - 07/23/2012 : 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
India
22461 Posts |
Posted - 07/23/2012 : 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 |
Edited by - madhivanan on 07/23/2012 09:01:10 |
|
|
|
emmim44
Yak Posting Veteran
65 Posts |
Posted - 07/24/2012 : 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
India
22461 Posts |
Posted - 07/24/2012 : 06:09:46
|
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 |
|
|
|
emmim44
Yak Posting Veteran
65 Posts |
Posted - 07/24/2012 : 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 - 07/25/2012 : 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
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
--------------------------------------------------------------------------------
|
|
|
| |
Topic |
|
|
|
Loop -SP Help?
