Help me please..Compare 2 tables

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)

Help me please..Compare 2 tables

Author

Topic

Cheluvaraj
Starting Member

1 Post

Posted - 2008-10-01 : 22:01:48

Hi Freinds, I have 2 tables
Table WorkMessage

WorkNo GUID Last_date
101 X120…… 2008-09-28 14:02:00
102 X121… 2008-09-29 14:02:00
103 X123….. 2008-09-30 09:02:00
104 X124… 2008-10-01 11:02:00
105 X125… 2008-10-01 14:02:00
104 X124 2008-10-01 15:02:00
103 X123 2008-10-01 18:02:00
Table PieTrace

GUID ErrorNo ErrorMessage Last_Date_Time
X120…… 0 blahblah 2008-09-28 14:45:00
X123… -23 blahblah 2008-09-30 10:02:00
X124… -1 blahblah 2008-10-01 11:15:00
X125 0 blahblha 2008-10-01 14:02:00
X124 0 blahblah 2008-10-01 16:02:00
X123 0 blahblah 2008-10-01 18:02:00
Below query for comparing 2 tables and extract the uncommon like GUID X121 doesn’t exists in the 2nd table and the GUID which are unsuccessful (has –ve values).

select isnull(pt.GUID,Wo.GUIS)as GUID,
pt.[ErrorNo] as Error
from PieTreace pt
full outer join WorkMessage Wm
on pt.GUID = Wm.GUID
where(pt.GUId is null) or (pt.[ErrorNo]<0)

Along with above query I want to extract those,ErroNo and GUID which are unsuccessful for first time and next time(It may be second time or third time)it’s successful, with the lastest date.(I have marked those repeating GUID with red colour.)

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 03:38:50

[code]SELECT pt.GUID,MAX(pt.ErrorNo),MAX(pt.Last_Date_Time)
FROM PieTerrace pt
LEFT JOIN WorkMessage wm
ON wm.GUID=pt.GUID
LEFT JOIN (SELECT GUID
FROM PieTerrace
GROUP BY GUID
HAVING SUM(CASE WHEN ErrorNo<0 THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN ErrorNo>0 THEN 1 ELSE 0 END) >0
AND MAX(CASE WHEN ErrorNo<0 THEN Last_Date_Time ELSE NULL END) < MAX(CASE WHEN ErrorNo>0 THEN Last_Date_Time ELSE NULL END))tmp
ON tmp.GUID = pt.GUID
WHERE (wm.GUID IS NULL
OR tmp.GUID IS NOT NULL)
GROUP BY pt.GUID
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources