exclude records

[Raul2005]

I need to exclude records on Record1 from record 2
base on the maximun date
Record1
ID Product Date
1 New Issue 2005-11-14
2 US High Grade 2005-11-17


Record 2
ID Product Date
1 New Issue 2005-11-02
1 New Issue 2005-11-04
1 New Issue 2005-11-14
2 US High Grade 2005-11-11
2 US High Grade 2005-11-12
2 US High Grade 2005-11-15
2 US High Grade 2005-11-17


Result
ID Product Date
1 New Issue 2005-11-02
1 New Issue 2005-11-04
2 US High Grade 2005-11-11
2 US High Grade 2005-11-12
2 US High Grade 2005-11-15


Thanks for any tips

 

[rac2]

Looking at your example data it seems like you already have the maximum dates for each Product in Record1, so it is just a matter of saying that you need any rows in Record2 that are not in Record1.

Why is the ID repeated? Does it the same thing as the Product?

Are Record1 and Record2 tables, or recordsets obtained from queries?

Is there another column with a key value that matches rows in Record1 with rows in Record2.

SELECT * 
FROM Record2 
WHERE Date NOT IN (
                    SELECT Date FROM Record1
                    WHERE ID = Record2.ID
)

 

[Raul2005]

That is what I as looking for..
Thanks

 

[Raul2005]

However what happens if the following situation occur.
The dates in record1 are the same, the query will fail .


declare @Record1 table(ID int, pname varchar(20),[date] datetime)
insert into @Record1 values (1,'Issue','2005-11-17 10:53:03.237')
insert into @Record1 values (2,'Grade','2005-11-17 10:53:03.237')


declare @Record2 table(ID int, pname varchar(20),[date] datetime)
insert into @Record2 values (1, 'Issue','2005-11-02 10:43:02.963')
insert into @Record2 values (1,'Issue','2005-11-04 10:43:02.963')
insert into @Record2 values (1,'Issue','2005-11-14 10:25:17.483')
insert into @Record2 values (2,'Grade','2005-11-11 10:43:02.963')
insert into @Record2 values (2,'Grade','2005-11-12 10:43:02.963')
insert into @Record2 values (2,'UGrade','2005-11-15 10:43:02.963')
insert into @Record2 values (2,'Grade','2005-11-17 10:53:03.237')




SELECT *
FROM @Record2 a
WHERE [date] NOT IN (
SELECT [date] FROM @Record1
WHERE ID = a.ID
)

Let me know if you have some ideas to avoid this situation
Thanks for your help.

 

[rac2]

Why is the ID repeated? Does it mean the same thing as the Product?

Are Record1 and Record2 tables, or recordsets obtained from queries?

Is there another column with a key value that matches rows in Record1 with rows in Record2.

Could you say more about what this means, "exclude records on Record1 from record 2 base on the maximun date"

 

[Raul2005]

All of them are views
Record1 returns all Maximum dates for an specific product the only thing I need from this is the ID because I want to use this to obtain data on record2. However record 2 will return all data including data from record1 which I don't need it.

 

[earthandfire]

Would this do what you want:

SELECT * 
FROM @Record2 a 
WHERE [date] NOT IN (SELECT max([date]) FROM @Record2
                    WHERE ID = a.ID
)

Hope this helps.

 

[earthandfire]

Or in the event that you only want to extract records from @Record2 where the ID exists in @Record1 then

SELECT * 
FROM @Record2 a
WHERE [date] NOT IN (SELECT max([date]) FROM @Record2
                    WHERE ID = a.ID AND ID IN (SELECT ID FROM @Record1))

seems to do what you need.

Hope this helps.

 

[Raul2005]

Thanks for your support guys.