Difference between 2 tables

Hi,

We have 2 tables as follows,

TableA
----
doc_id ver_id ref_date

TableB
----
doc_id ver_id ref_min_date

We need to find out the records that exists in TableA but not in TableB
and also to get the minimum of TableA.ref_date from the result.

For example,

TableA has the following rows
doc_id ver_id ref_date
100 1 10-AUG-2006
100 2 12-AUG-2006
100 2 13-AUG-2006
100 2 14-AUG-2006
100 2 15-AUG-2006
200 1 17-AUG-2006
300 1 18-AUG-2006

TableB has the following rows
doc_id ver_id ref_date
100 1 10-AUG-2006
200 1 10-AUG-2006

Result should be as follows
doc_id ver_id ref_date
100 2 12-AUG-2006
300 1 18-AUG-2006

Please help us in writing the query

Thanks
AshokHi Ashok,

You may like to try this.

Select A.doc_id,A.ver_id ,Min(A.ref_date) From TableA A
Where Not Exists ( Select 1 From TableB B where A.doc_id=B.doc_id
And A.ver_id=B.ver_id
And A.ref_date=B.ref_date
) Group By A.doc_id,A.ver_id ;

I hope this helps.

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
http://sqloracle.tripod.comsql

Developing Stored Procedures using Management Studio Express

Hi,

I am new to SQL server 2005. I am developing stored procedures as follows in Management Studio:

1)Right click Stored Procedures folder on a database
2)Select "New Stored Procedure"
3)Write the query in a .sql file
4)Execute the query.

Now in this model, I dont seem to have a develop->test->develop and then deploy, type of development cycle. The problem is each time I execute a query it is deployed and re-executing it results in an error like: "There is already an object named 'NewEmployee' in the database." Which forces you to manually delete the SP and re-execute your query. It doesn't give you a chance to test your SP logic before deploying. Contrary to this, If I developed CLR SP's in Visual Studio, I would test/debug the procedure, and then "deploy" it later when I'm finished with it. Can I not do this in Management Studio?

Thanks,
Bahadir

right click the existing stored proc

click on "script stored procedure as">alter>"new query editor"

modify the scripts

and run

|||Doh! I think this was a bit stupid question. More clever next time :)

Thanks,
Bahadir

Developing Stored Procedures using Management Studio Express

Hi,

I am new to SQL server 2005. I am developing stored procedures as follows in Management Studio:

1)Right click Stored Procedures folder on a database
2)Select "New Stored Procedure"
3)Write the query in a .sql file
4)Execute the query.

Now in this model, I dont seem to have a develop->test->develop and then deploy, type of development cycle. The problem is each time I execute a query it is deployed and re-executing it results in an error like: "There is already an object named 'NewEmployee' in the database." Which forces you to manually delete the SP and re-execute your query. It doesn't give you a chance to test your SP logic before deploying. Contrary to this, If I developed CLR SP's in Visual Studio, I would test/debug the procedure, and then "deploy" it later when I'm finished with it. Can I not do this in Management Studio?

Thanks,
Bahadir

right click the existing stored proc

click on "script stored procedure as">alter>"new query editor"

modify the scripts

and run

|||Doh! I think this was a bit stupid question. More clever next time :)

Thanks,
Bahadir