SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Items_TEST](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Location] [nvarchar](5) NOT NULL,
	[ProdCode] [nvarchar](5) NOT NULL,
	[Item] [nvarchar](6) NULL,
	[Customer] [nvarchar](10) NOT NULL,
	[Date_Effective] [smalldatetime] NOT NULL,
	[Source] [nvarchar](5) NOT NULL,
	[User] [nvarchar](50) NULL,
	[Date_Exported] [smalldatetime] NULL,
	[Time_Exported] [nvarchar](8) NULL,
	[Add1] [numeric](7, 2) NOT NULL CONSTRAINT [DF_Items_TEST_Add1]  DEFAULT ((0.00)),
	[Add2] [numeric](7, 2) NOT NULL CONSTRAINT [DF_Items_TEST_Add2]  DEFAULT ((0.00)),
 CONSTRAINT [PK_Items_TEST] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

truncate table dbo.Items_TEST
;

INSERT INTO dbo.Items_TEST
(
Location, ProdCode, Item, Customer, Date_Effective, Source, [User], Date_Exported, Time_Exported, Add1, Add2
)
VALUES ('9999', '3004', NULL, 'EEVEY', '2012-09-16', 'FV', 'SM', '2012-11-19', '12:01:29', '0.00', '3.00')
;

INSERT INTO dbo.Items_TEST
(
Location, ProdCode, Item, Customer, Date_Effective, Source, [User], Date_Exported, Time_Exported, Add1, Add2
)
VALUES ('9999', '3004', NULL, 'EEVEY', '2012-09-17', 'FV', 'SM', '2012-12-20', '09:14:54', '4.00', '0.00')
;

INSERT INTO dbo.Items_TEST
(
Location, ProdCode, Item, Customer, Date_Effective, Source, [User], Date_Exported, Time_Exported, Add1, Add2
)
VALUES ('9999', '3004', NULL, 'EEVEY', '2012-09-18', 'FV', 'SM', '2013-01-10', '14:36:23', '5.00', '0.00')
;

INSERT INTO dbo.Items_TEST
(
Location, ProdCode, Item, Customer, Date_Effective, Source, [User], Date_Exported, Time_Exported, Add1, Add2
)
VALUES ('5544', '3002', NULL, 'PESS', '2012-09-17', 'FV', 'SM', '2013-01-10', '14:36:23', '1.50', '0.00')
;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Staging1](
	[Location] [nvarchar](5) NOT NULL,
	[ProdCode] [nvarchar](5) NOT NULL,
	[Item] [nvarchar](6) NULL,
	[Customer] [nvarchar](10) NOT NULL,
	[Date_Effective] [smalldatetime] NOT NULL,
	[Source] [nvarchar](5) NOT NULL,
	[User] [nvarchar](50) NULL,
	[Date_Exported] [smalldatetime] NULL,
	[Time_Exported] [nvarchar](8) NULL,
	[Add1] [numeric](7, 2) NOT NULL CONSTRAINT [DF_Staging1_Add1]  DEFAULT ((0.00)),
	[Add2] [numeric](7, 2) NOT NULL CONSTRAINT [DF_Staging1_Add2]  DEFAULT ((0.00))
) ON [PRIMARY]

TRUNCATE TABLE dbo.Staging1
;

INSERT INTO dbo.Staging1
(
Location, ProdCode, Item, Customer, Date_Effective, Source, [User], Date_Exported, Time_Exported, Add1, Add2
)
VALUES ('5544', '3002', NULL, 'PESSY', '2012-09-21', 'FV', 'SM', '2013-01-19', '14:08:31', '0.50', '0.40')
;

INSERT INTO dbo.Staging1
(
Location, ProdCode, Item, Customer, Date_Effective, Source, [User], Date_Exported, Time_Exported, Add1, Add2
)
VALUES ('9999', '3004', NULL, 'EEVEY', '2012-09-21', 'FV', 'SM', '2013-01-19', '14:08:31', '7.00', '0.00')
;

update it
set Add1 = s.Add1
 ,Add2 = s.Add2
from dbo.items_test it
join dbo.Staging1 s on it.Location = s.Location
 and it.ProdCode = s.ProdCode
 and it.Customer = s.Customer
where it.Date_Effective = (
  select max(date_effective)
  from dbo.Items_TEST t
  where t.Location = it.Location
   and t.ProdCode = it.ProdCode
   and t.Customer = it.Customer
  )

with MyCTE as (
	select Location, ProdCode, Item, Customer, Date_Effective, Source, [User], Date_Exported, Time_Exported, Add1, Add2,
	row_number() over (partition by Location, ProdCode, Customer order by Date_Effective desc) AS RowNum
	from Items_TEST)
update MyCTE
SET MyCTE.Item = Staging1.Item,
MyCTE.Date_Effective = Staging1.Date_Effective,
MyCTE.Source = Staging1.Source,
MyCTE.[User] = Staging1.[User],
MyCTE.Date_Exported = Staging1.Date_Exported,
MyCTE.Time_Exported = Staging1.Time_Exported,
MyCTE.Add1 = Staging1.Add1,
MyCTE.Add2 = Staging1.Add2
from Staging1 inner join MyCTE on Staging1.Customer = MyCTE.Customer 
	AND Staging1.Location = MyCTE.Location 
	AND Staging1.ProdCode = MyCTE.ProdCode
	AND MyCTE.RowNum = 1