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

General SQL Server Forums

New to SQL Server Programming

Comparision

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2011-10-07 : 06:58:28

Dear I am Working on Comparision

My Date Is Like This

Table A
------------
(ItemNo) (Date)
SE00013 2011-09-02 00:00:00
SE00429 2011-09-02 00:00:00
SE00060 2011-09-03 00:00:00
SE00071 2011-09-03 00:00:00
SE00071 2011-09-03 00:00:00
SE00130 2011-09-03 00:00:00

TABLE B
--------

Table A
------------
(ItemNo) (Date)
SE00013 2011-09-02 00:00:00
SE00013 2011-09-02 00:00:00
SE00060 2011-09-03 00:00:00
SE00071 2011-09-03 00:00:00
SE00130 2011-09-03 00:00:00

I want To Compare these two tables Like This

Table A
------------
(ItemNo) (Date)
SE00013 2011-09-02 00:00:00 SE00013 2011-09-02 00:00:00
NUll SE00013 2011-09-02 00:00:00
SE00429 2011-09-02 00:00:00 NULL
SE00060 2011-09-03 00:00:00 SE00060 2011-09-03 00:00:00
SE00071 2011-09-03 00:00:00 SE00071 2011-09-03 00:00:00
SE00071 2011-09-03 00:00:00 NULL
SE00130 2011-09-03 00:00:00 SE00130 2011-09-03 00:00:00

any Idea???
THANKS IN ADVANCE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 07:02:01

[code]
SELECT a.*,b.*
FROM TableA a
FULL OUTER JOIN TableB b
ON a.ItemNo=b.ItemNo
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

asif372
Posting Yak Master

100 Posts

Posted - 2011-10-07 : 07:21:41

Dear Visak I Have Check Your Query but it Dublicates Record Like this

(ItemNo) (Date) (ItemNo) (Date)
SE00060 2011-09-03 00:00:00 SE00060 2011-09-03 00:00:00
SE00071 2011-09-03 00:00:00 SE00071 2011-09-03 00:00:00
SE00071 2011-09-03 00:00:00 SE00071 2011-09-03 00:00:00
SE00071 2011-09-03 00:00:00 SE00071 2011-09-03 00:00:00
SE00071 2011-09-03 00:00:00 SE00071 2011-09-03 00:00:00

I dont want Records to be Duplicates

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 07:23:09

[code]
SELECT a.*,b.*
FROM (SELECT DISTINCT ItemNo,Date FROM TableA) a
FULL OUTER JOIN (SELECT DISTINCT ItemNo,Date FROM TableB )b
ON a.ItemNo=b.ItemNo
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

asif372
Posting Yak Master

100 Posts

Posted - 2011-10-07 : 07:40:24

Still facing the Same Problem
See my Required Data

(ItemNo) (Date) (ItemNo) (Date)
SE00071 2011-09-03 00:00:00 SE00071 2011-09-03 00:00:00
SE00071 2011-09-03 00:00:00 NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 07:46:33

sorry didnt get that. you told to remove duplicates but now posted resulset itself contains duplicates. please explain what exactly you're trying to achieve.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

asif372
Posting Yak Master

100 Posts

Posted - 2011-10-07 : 08:00:37

Thanks For your Great Time
I want To Select All Data from table A and matched Data from table B
where a.Date = b.date and a.itemno = b.itemno
just like this

Table A
------------
(ItemNo) (Date)
SE00071 2011-09-03 00:00:00
SE00071 2011-09-03 00:00:00

Table B
------------
(ItemNo) (Date)
SE00071 2011-09-03 00:00:00

RESULTANT TABLE
-----------------
(ItemNo) (Date) (ItemNo) (Date)

SE00071 2011-09-03 00:00:00 SE00071 2011-09-03 00:00:00
SE00071 2011-09-03 00:00:00

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 12:15:24

[code]
SELECT a.*,b.*
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY ItemNo ORDER BY Date) AS Rn,ItemNo,Date FROM TableA) a
FULL OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY ItemNo ORDER BY Date) AS Rn,ItemNo,Date FROM TableB )b
ON a.ItemNo=b.ItemNo
AND a.Rn = b.Rn
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-10 : 04:36:14

SELECT a.*,b.*
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY ItemNo ORDER BY Date) AS Sequence,ItemNo,Date FROM #a) a
FULL JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY ItemNo ORDER BY Date) AS Sequence,ItemNo,Date FROM #b)b
ON a.ItemNo=b.ItemNo
AND a.Sequence = b.Sequence

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 04:41:28

quote:
Originally posted by jassi.singh

SELECT a.*,b.*
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY ItemNo ORDER BY Date) AS Sequence,ItemNo,Date FROM #a) a
FULL JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY ItemNo ORDER BY Date) AS Sequence,ItemNo,Date FROM #b)b
ON a.ItemNo=b.ItemNo
AND a.Sequence = b.Sequence

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Whats the use in duplicating others suggestions like this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-10 : 23:36:30

quote:
Originally posted by visakh16

quote:
Originally posted by jassi.singh

SELECT a.*,b.*
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY ItemNo ORDER BY Date) AS Sequence,ItemNo,Date FROM #a) a
FULL JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY ItemNo ORDER BY Date) AS Sequence,ItemNo,Date FROM #b)b
ON a.ItemNo=b.ItemNo
AND a.Sequence = b.Sequence

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Whats the use in duplicating others suggestions like this?

Quick and easy way to increase your post count.

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 01:05:18

quote:
Originally posted by GilaMonster

quote:
Originally posted by visakh16

quote:
Originally posted by jassi.singh

SELECT a.*,b.*
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY ItemNo ORDER BY Date) AS Sequence,ItemNo,Date FROM #a) a
FULL JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY ItemNo ORDER BY Date) AS Sequence,ItemNo,Date FROM #b)b
ON a.ItemNo=b.ItemNo
AND a.Sequence = b.Sequence

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Whats the use in duplicating others suggestions like this?

Quick and easy way to increase your post count.

--
Gail Shaw
SQL Server MVP

Thats true

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources