Row by Row comparison for last 2 records.

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

Transact-SQL (2005)

Row by Row comparison for last 2 records.

Author

Topic

srujanavinnakota
Starting Member

34 Posts

Posted - 2010-09-09 : 14:48:06

Hi, Can anyone help with query

I have an audit table in the following format.

Id customerId oldval newval Statusdate
1 1 1 12 1/10/2010
2 1 12 53 1/12/2010
3 1 53 55 2/14/2010
4 1 55 57 3/15/2010
5 2 1 51 1/10/2010
6 2 51 53 3/15/2010
7 2 53 57 4/1/2010
8 3 1 51 6/1/2010
9 3 51 53 6/15/2010
10 3 53 56 7/14/2010
11 3 56 57 7/21/2010

From the above table I need to pull the customer's most recent record that has
oldval=53 and newval=57 and the immediate prior record to that record should be oldval=51 and newval=53.

In other words records order should be like below
oldval new val
51 53
53 57

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-09-09 : 15:29:31

Try this

declare @t TABLE (
Id smallint, customerId smallint, oldval smallint, newval smallint, Statusdate smalldatetime)
insert into @t
select 1, 1 ,1 ,12 ,'1/10/2010' union all
select 2, 1 ,12 ,53 ,'1/12/2010' union all
select 3, 1 ,53 ,55 ,'2/14/2010' union all
select 4, 1 ,55 ,57 ,'3/15/2010' union all
select 5, 2 ,1 ,51 ,'1/10/2010' union all
select 6, 2 ,51 ,53 ,'3/15/2010' union all
select 7 ,2 ,53 ,57 ,'4/1/2010' union all
select 8 ,3 ,1 ,51 ,'6/1/2010' union all
select 9 ,3 ,51 ,53 ,'6/15/2010' union all
select 10 ,3, 53 ,56 ,'7/14/2010' union all
select 11 ,3, 56, 57 ,'7/21/2010'

;with cte as
(select CustomerID, OldVal, NewVal, ID
from @t
where oldval = 53 and newval = 57)

select t.CustomerID, t.OldVal, t.NewVal, t.ID
from @t t
inner join cte c
on t.customerId = c.customerId
and t.Id = c.Id - 1
Union all
select CustomerID, OldVal, NewVal, ID
from cte

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-09 : 16:25:17

and the immediate prior record to that record should be oldval=51 and newval=53.

Assume that it is based on statusdate column.

Try this ..

declare @t TABLE (
Id smallint, customerId smallint, oldval smallint, newval smallint, Statusdate smalldatetime)
insert into @t
select 1, 1 ,1 ,12 ,'1/10/2010' union all
select 2, 1 ,12 ,53 ,'1/12/2010' union all
select 3, 1 ,53 ,55 ,'2/14/2010' union all
select 4, 1 ,55 ,57 ,'3/15/2010' union all
select 5, 2 ,1 ,51 ,'1/10/2010' union all
select 6, 2 ,51 ,53 ,'3/15/2010' union all
select 7 ,2 ,53 ,57 ,'4/1/2010' union all
select 8 ,3 ,1 ,51 ,'6/1/2010' union all
select 9 ,3 ,51 ,53 ,'6/15/2010' union all
select 10 ,3, 53 ,56 ,'7/14/2010' union all
select 11 ,3, 56, 57 ,'7/21/2010'
-- borrow from MSquared

;with temp as (select *, rn = row_number() over(order by statusdate) from @t)
select t1.* from temp t1 join temp t2 on
(t1.rn=t2.rn-1 and (t1.oldval=51 and t1.newval=53 and t2.oldval=53 and t2.newval=57)) or (t1.rn=t2.rn+1 and (t1.oldval=53 and t1.newval=57 and t2.oldval=51 and t2.newval=53))
order by id

srujanavinnakota
Starting Member

34 Posts

Posted - 2010-09-09 : 16:42:51

From the sample data I need get only customerID 2 records.
Since the on 4/1/2010 values are oldval=53 and newval=57
and immediate prior to that was on 3/15/2010 and values are oldval=51 and newval=53.
it is based on statusdate column per customer.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-09 : 22:52:00

declare @t TABLE (
Id smallint, customerId smallint, oldval smallint, newval smallint, Statusdate smalldatetime)
insert into @t
select 1, 1 ,1 ,12 ,'1/10/2010' union all
select 2, 1 ,12 ,53 ,'1/12/2010' union all
select 3, 1 ,53 ,55 ,'2/14/2010' union all
select 4, 1 ,55 ,57 ,'3/15/2010' union all
select 5, 2 ,1 ,51 ,'1/10/2010' union all
select 6, 2 ,51 ,53 ,'3/15/2010' union all
select 7 ,2 ,53 ,57 ,'4/1/2010' union all
select 8 ,3 ,1 ,51 ,'6/1/2010' union all
select 9 ,3 ,51 ,53 ,'6/15/2010' union all
select 10 ,3, 53 ,56 ,'7/14/2010' union all
select 11 ,3, 56, 57 ,'7/21/2010'
-- borrow from MSquared

;with temp as (select *, rn = row_number() over(partition by customerId order by statusdate) from @t)
select t1.* from temp t1 join temp t2 on t1.customerId=t2.customerId and
((t1.rn=t2.rn-1 and (t1.oldval=51 and t1.newval=53 and t2.oldval=53 and t2.newval=57)) or (t1.rn=t2.rn+1 and (t1.oldval=53 and t1.newval=57 and t2.oldval=51 and t2.newval=53)))
order by id

--result
Id customerId oldval newval Statusdate rn
------ ---------- ------ ------ ----------------------- --------------------
6 2 51 53 2010-03-15 00:00:00 2
7 2 53 57 2010-04-01 00:00:00 3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources