row by row comparision - 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 2005 Forums

Transact-SQL (2005)

row by row comparision

Author

Topic

sushil
Starting Member

11 Posts

Posted - 2010-06-02 : 16:15:53

Hi All,

I need to compare row by row without using cursor for SQL. IF there is any changes on the TR1, it has to be captured on which TR1 change for a giving year.

INPUT
Year TR1 TransactionID
2008-09 5 263591409RE01
2008-09 5 263591409RE02
2008-09 5 263591409RE03
2008-09 0 263591409RE04
2008-09 5 263591409RE05
2009-10 787 263591409RE01
2009-10 787 263591409RE02
2009-10 787 263591409RE03
2009-10 787 263591409RE04
2009-10 787 263591409RE05
2009-10 399 263591409RE06
2009-10 399 263591409RE07
2009-10 399 263591409RE08
2009-10 36 263591409RE09

OUTPUT
Year TR1 TransactionID NewTR1 NewTrasactionID
2008-09 5 263591409RE03 0 263591409RE04
2008-09 0 263591409RE04 5 263591409RE05
2009-10 787 263591409RE05 399 263591409RE06
2009-10 399 263591409RE08 36 263591409RE09

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-02 : 17:00:40

What defines the order? Is it Year or Year and TransactionID or just TransactionID?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-02 : 17:06:14

Maybe there's a better way...but this?

select a.Year,a.TR1,a.TransactionID,b.TR1,b.TransactionID  from 
(
select row_number () over(partition by Year order by TransactionID) as seq, * from Table1 
) a
inner join
(
select row_number () over(partition by Year order by TransactionID) as seq, * from Table1 
) b
on a.Year = b.Year and a.seq + 1 = b.seq and a.TR1 <> b.TR1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-02 : 17:07:41

Here is one way:

-- Setup Sample Data
DECLARE @Table TABLE (Year VARCHAR(10), TR1 INT, TransactionID VARCHAR(20))
INSERT @Table
SELECT '2008-09', 5, '263591409RE01' 
UNION ALL SELECT '2008-09', 5, '263591409RE02'
UNION ALL SELECT '2008-09', 5, '263591409RE03' 
UNION ALL SELECT '2008-09', 0, '263591409RE04' 
UNION ALL SELECT '2008-09', 5, '263591409RE05' 
UNION ALL SELECT '2009-10', 787, '263591409RE01' 
UNION ALL SELECT '2009-10', 787, '263591409RE02' 
UNION ALL SELECT '2009-10', 787, '263591409RE03' 
UNION ALL SELECT '2009-10', 787, '263591409RE04' 
UNION ALL SELECT '2009-10', 787, '263591409RE05' 
UNION ALL SELECT '2009-10', 399, '263591409RE06' 
UNION ALL SELECT '2009-10', 399, '263591409RE07' 
UNION ALL SELECT '2009-10', 399, '263591409RE08' 
UNION ALL SELECT '2009-10', 36, '263591409RE09' 

-- Query
;WITH cte AS
(
	SELECT *, ROW_NUMBER() OVER (ORDER BY CAST(Year + '-01' AS DATETIME), TransactionID) AS RowNum
	FROM @Table
)


SELECT 
	*
FROM 
	cte AS A
INNER JOIN 
	cte AS B
	ON A.RowNum = B.RowNum - 1
	AND A.Year = B.Year
WHERE 
	A.TR1 <> B.TR1

Obviously replace *'s with proper column names...

EDIT.. Dang too slow :)

sushil
Starting Member

11 Posts

Posted - 2010-06-02 : 17:36:39

wow, its working now, I do not know how to express my gratitude . God bless you alllll!!!!!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-06-03 : 08:10:20

One questions why you CANNOT use a cursor. We understand why you SHOULD not.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

coagulance
Yak Posting Veteran

78 Posts

Posted - 2010-06-03 : 08:49:39

I have a similar requirement where i need to have some calculated fields in the query result like TR1-TR2 without using CTE on the result set again?

Sachin.Nand

2937 Posts

Posted - 2010-06-03 : 09:08:17


DECLARE @Table TABLE (id int identity(1,1),Year VARCHAR(10), TR1 INT, TransactionID VARCHAR(20))
INSERT @Table
SELECT '2008-09', 5, '263591409RE01' 
UNION ALL SELECT '2008-09', 5, '263591409RE02'
UNION ALL SELECT '2008-09', 5, '263591409RE03' 
UNION ALL SELECT '2008-09', 0, '263591409RE04' 
UNION ALL SELECT '2008-09', 5, '263591409RE05' 
UNION ALL SELECT '2009-10', 787, '263591409RE01' 
UNION ALL SELECT '2009-10', 787, '263591409RE02' 
UNION ALL SELECT '2009-10', 787, '263591409RE03' 
UNION ALL SELECT '2009-10', 787, '263591409RE04' 
UNION ALL SELECT '2009-10', 787, '263591409RE05' 
UNION ALL SELECT '2009-10', 399, '263591409RE06' 
UNION ALL SELECT '2009-10', 399, '263591409RE07' 
UNION ALL SELECT '2009-10', 399, '263591409RE08' 
UNION ALL SELECT '2009-10', 36, '263591409RE09' 


Select * from @Table t1
Cross Apply(
Select top 1 TR1,TransactionID From @Table t2 Where t1.TR1<>t2.TR1 
And t1.id=t2.id+1 And t1.Year=t2.Year Order by year
)t3

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources