Update using Max() value on multi join query

• DerbyNeal

  Hall of Fame

  Points: 3854

  More actions

  September 13, 2012 at 2:29 am

  #264065

  Hi,

  I have a problem where I need to update a table (Results). The one thing I can't get my head around is the part concerning the ct.DateStart >= ao.Date_Effective line in the join. There could be multiple ao.Date_Effective records in this instance, and i need to join on the row with the maximum date value. How do i apply this?

  The tables/values are...

  --DATEFORMAT YMD

  --RESULTS

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[Results](

  [FV_Flag] [nvarchar](1) NULL,

  [EX16] [numeric](7, 2) NULL,

  [CustBookId] [nvarchar](5) NULL,

  [FormatNumber] [nvarchar](5) NULL,

  [ContractId] [nvarchar](10) NULL,

  [Source] [nvarchar](5) NULL

  ) ON [PRIMARY]

  ;

  INSERT INTO Results

  (FV_Flag, EX16, CustBookId, FormatNumber, ContractId, Source)

  VALUES('x', '0.00', 'c134', '5170', '2345', 'p');

  INSERT INTO Results

  (FV_Flag, EX16, CustBookId, FormatNumber, ContractId, Source)

  VALUES('x', '0.00', 'c135', '5170', '', 'p');

  INSERT INTO Results

  (FV_Flag, EX16, CustBookId, FormatNumber, ContractId, Source)

  VALUES('x', '0.00', 'c136', '4234', '3344', 'p');

  --AddOns

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[Addons](

  [Customer] [nvarchar](10) NULL,

  [FmNo] [nvarchar](10) NULL,

  [Date_Effective] [smalldatetime] NULL,

  [EX16] [numeric](7, 2) NULL,

  [Mill] [nvarchar](4) NULL

  ) ON [PRIMARY]

  ;

  INSERT INTO AddOns

  (Customer, FmNo, Date_Effective, EX16, Mill)

  VALUES('c134','5170','2012/09/02','1', '9999');

  INSERT INTO AddOns

  (Customer, FmNo, Date_Effective, EX16, Mill)

  VALUES('c134','5170','2012/09/07','2', '9999');

  INSERT INTO AddOns

  (Customer, FmNo, Date_Effective, EX16, Mill)

  VALUES('c134','5170','2012/09/10','4', '9999');

  INSERT INTO AddOns

  (Customer, FmNo, Date_Effective, EX16, Mill)

  VALUES('c135','5170','2012/09/02','2', '9999');

  INSERT INTO AddOns

  (Customer, FmNo, Date_Effective, EX16, Mill)

  VALUES('c136','4234','2012/09/12','5', '9999');

  --ContractTable

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[ContractTable](

  [ContractId] [nvarchar](50) NULL,

  [DateStart] [smalldatetime] NULL

  ) ON [PRIMARY]

  ;

  INSERT INTO ContractTable

  (ContractId, DateStart)

  VALUES('2345', '2012/09/09');

  INSERT INTO ContractTable

  (ContractId, DateStart)

  VALUES('3344', '2012/09/11');

  The join is...

  UPDATE tr

  SET FV_Flag = 'N',

  EX16 = ao.EX16

  FROM @tblResults tr

  LEFT JOIN Market_Prices.dbo.BV_AddOns ao WITH ( NOLOCK) ON

  ao.Customer = tr.CustBookId

  AND ao.FmNo = tr.SIG_FormatNumber

  LEFT JOIN SIG_CONTRACTTABLE ct WITH ( NOLOCK) ON

  ct.CONTRACTID = tr.SIG_ContractId

  AND ct.DateStart >= ao.Date_Effective --Max Date_Effective

  WHERE ao.Mill = '9999'

  AND tr.Source = 'P'

  AND tr.SIG_ContractId != ''

  AND tr.CustBookId IS NOT NULL

  The only row that should be affected is where the CustBookId = 'c134'.

  c135 is NOT updated as there is no ContractId

  c136 is NOT updated as DateStart is less than the Date_Effective in the query join

  Any ideas please?

  Thanks in advance,

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 13, 2012 at 3:02 am

  #1537081

  UPDATE tr SET

  FV_Flag = 'N',

  EX16 = ao.EX16

  FROM Results tr

  CROSS APPLY (

  SELECT TOP 1

  ao.EX16,

  ao.Date_Effective

  FROM AddOns ao

  WHERE ao.Customer = tr.CustBookId

  AND ao.FmNo = tr.SIG_FormatNumber

  AND ao.Mill = '9999'

  ORDER BY Date_Effective DESC

  ) ao

  LEFT JOIN ContractTable ct

  ON ct.CONTRACTID = tr.SIG_ContractId

  AND ct.DateStart >= ao.Date_Effective --Max Date_Effective

  WHERE tr.Source = 'P'

  AND tr.SIG_ContractId != ''

  AND tr.CustBookId IS NOT NULL

  ContractTable is not required in this query.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden