Find Differences - 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)

Find Differences

Author

Topic

00kevin
Yak Posting Veteran

78 Posts

Posted - 2009-04-15 : 12:55:29

Hi,

I need to write a query to find all the checks where the name is different then previous checks issued under an agreement


Date          AgrId       ChkId   Name
----------    ---------   ------  --------
2004-03-11    102252271	  100061  Eli NewName
2004-03-11    102252271	  100842  Eli OldName
2004-11-11    102252271	  100843  Eli OldName

What I need is a result set that looks like the following



AgrId      ChkId   NewName      OldName
---------  ------  --------     ------------
102252271  100061  Eli NewName  Eli OldName

Any hints?

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-15 : 13:13:52

I don't have a SQL server in front of me but this should work:

SELECT AgrId, Name, (SELECT TOP 1 Name FROM [table] as subTable WHERE subTable.AgrId = qryTbl.AgrId GROUP BY Name Order By Date DESC) as OldName, (SELECT TOP 1 chkId FROM [table] as subTable WHERE subTable.AgrId = qryTbl.AgrId ORDER BY Date ASC) as OldName
FROM
(SELECT DISTINCT AgrId, Name FROM [table] GROUP BY AgrId, Name ORDER BY Date ASC) as qryTbl

There is a problem because how do you know which name is the old name and which one is the new name? I ordered by date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 13:14:50

[code]
SELECT t.AgrId,t.ChkId,t.Name AS NewName,t1.Name AS OldName
FROM Table t
CROSS APPLY (SELECT TOP 1 Name
FROM Table
WHERE AgrId=t.AgrId
AND ChkId >t.ChkId
ORDER BY ChkId ASC) t1
WHERE t1.Name <> t.Name
[/code]

00kevin
Yak Posting Veteran

78 Posts

Posted - 2009-04-15 : 13:31:07

thanks whitefang...

the sample date data wasn't correct.

Date AgrId ChkId Name
---------- --------- ------ --------
2005-03-11 102252271 100061 Eli NewName
2003-03-11 102252271 100843 Eli OldName
2002-11-11 102252271 100800 Eli OldName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 14:14:52

did you try my suggestion?

00kevin
Yak Posting Veteran

78 Posts

Posted - 2009-04-15 : 14:27:45

hi visakh16

yes your suggestion works. the problem is that I just found out that the query must be run on sql 2000.

jmpierce30
Starting Member

7 Posts

Posted - 2009-04-15 : 15:30:53

just do

SELECT ChkId FROM Checks t1
INNER JOIN Checks t2 ON t1.AgrID = t2.AgrID AND t1.Name != t2.Name

See if that will work for you :-)

while (I != Understand)
{
KickMe.Hard;
}

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-16 : 03:58:38

quote:
Originally posted by jmpierce30

just do

SELECT ChkId FROM Checks t1
INNER JOIN Checks t2 ON t1.AgrID = t2.AgrID AND t1.Name != t2.Name

See if that will work for you :-)

while (I != Understand)
{
KickMe.Hard;
}

That wont work

Madhivanan

Failing to plan is Planning to fail

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-16 : 04:36:15

Hi,

declare @table1 table (date datetime,Agrid int,chkId int,name varchar(18) )

insert into @table1 values('03-11-2004',102252271,100061, 'Eli NewName')
insert into @table1 values('03-11-2004',102252271,100842, 'Eli OldName')
insert into @table1 values('11-11-2004',102252271,100843, 'Eli OldName') ;

with abc(date,agrid,chkid,name,id) as
(
select date,agrid,chkid,name, row_number() over (order by date asc) id from @table1
)

select date,agrid,chkid,name new_name,(select name from abc t2 where t2.id=t1.id-1) old_name
from abc t1
where name <>(select name from abc t2 where t2.id=t1.id-1)

Kunal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 11:07:51

quote:
Originally posted by kunal.mehta

Hi,

declare @table1 table (date datetime,Agrid int,chkId int,name varchar(18) )

insert into @table1 values('03-11-2004',102252271,100061, 'Eli NewName')
insert into @table1 values('03-11-2004',102252271,100842, 'Eli OldName')
insert into @table1 values('11-11-2004',102252271,100843, 'Eli OldName') ;

with abc(date,agrid,chkid,name,id) as
(
select date,agrid,chkid,name, row_number() over (order by date asc) id from @table1
)

select date,agrid,chkid,name new_name,(select name from abc t2 where t2.id=t1.id-1) old_name
from abc t1
where name <>(select name from abc t2 where t2.id=t1.id-1)

Kunal

this still wont work in sql 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 11:09:55

quote:
Originally posted by 00kevin

hi visakh16

yes your suggestion works. the problem is that I just found out that the query must be run on sql 2000.


SELECT AgrId,ChkId,NewName,OldName
FROM
(
SELECT t.AgrId,t.ChkId,t.Name AS NewName,
(SELECT TOP 1 Name 
             FROM Table
             WHERE AgrId=t.AgrId
             AND ChkId >t.ChkId
             ORDER BY ChkId ASC) AS OldName
FROM Table t
)r
WHERE r.NewName <> r.OldName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources