February 6, 2009 at 7:48 pm
This code shows us records that are duplicates when compared between the two tables and only those duplicates.
What we want to see is the latest (newest) records compared between the two tables by RM_BCODE and by RM_DateTime and only those latest (newest) records.
Any ideas?
SELECT TOP 100 PERCENT c.RM_BCODE AS VeneerA1, c.RM_Location AS VeneerA2, o.RM_BCODE AS VeneerB1, o.RM_Location AS VeneerB2,
c.RM_DateTime AS VeneerA3, o.RM_DateTime AS VeneerB3
FROM dbo.tbl_RM_Materials c FULL OUTER JOIN
Veneer2.dbo.tbl_RM_Materials o ON o.RM_BCODE = o.RM_BCODE AND c.RM_BCODE = o.RM_BCODE
WHERE (c.RM_DateTime > o.RM_DateTime) OR
(c.RM_DateTime IS NULL) OR
(o.RM_DateTime > c.RM_DateTime) OR
(o.RM_DateTime IS NULL)
February 9, 2009 at 6:12 am
Can you post the table structures and some sample data? Please see the links in my signature for advice on posting this information.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 9, 2009 at 10:00 am
I have put together what I think you are asking for:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_RM_Materials]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_RM_Materials]
GO
CREATE TABLE [dbo].[tbl_RM_Materials] (
[RM_ID] [int] NULL ,
[RM_BCODE] [nchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RM_DateTime] [datetime] NULL ,
[RM_PurchOrd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RM_Location] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RM_Supplier] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RM_Grade] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RM_Thickness] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RM_Quantity] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RM_Status] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RM_Product] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Now for the insert code:
INSERT INTO #tbl_RM_Materials
(RM_ID, RM_BCODE, RM_DateTime, RM_PurchOrd, RM_Location, RM_Supplier, RM_Thickness, RM_Quantity, RM_Status, RM_Product )
SELECT '1','123','Oct 17 2007 12:00AM','0','W23', 'West Pine', '.125', '225', 'Raw/wet', '0', UNION ALL
SELECT '1','1234','Oct 17 2007 12:00AM','0','W25', 'North Pine', '.167', '200', 'Raw', '0', UNION ALL
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply