Help needed with query where matching or last date found

• DerbyNeal

  Hall of Fame

  Points: 3854

  More actions

  October 5, 2011 at 3:45 am

  #246073

  Hi,

  I have the following SQL query with 2 issues:

  SET DATEFORMAT YMD

  SELECT *

  FROM AX4KWT_LIVE..PurchTable pt

  JOIN AX4KWT_LIVE..PurchLine pl WITH( NOLOCK) ON

  pt.PurchId = pl.PurchId

  AND pl.DataAreaId = pt.DataAreaId

  LEFT JOIN Market_Prices..Market_Prices_KWT mpk WITH( NOLOCK) ON

  mpk.Product_Code = pl.ItemId

  AND MONTH(pl.VCTMoveStartDate) = MONTH(pl.VCTMoveEndDate)

  AND YEAR(pl.VCTMoveStartDate) = YEAR(pl.VCTMoveEndDate)

  LEFT JOIN Market_Prices..Market_Prices_Reference mpr WITH( NOLOCK) ON

  RTRIM(LTRIM(mpr.AX_Location_Code)) = RTRIM(LTRIM(pl.VCTCollectionLocation))

  WHERE

  pt.DataAreaId = 'ktl'

  AND mpk.Start_Date <= pl.VCTMoveStartDate

  AND mpk.End_Date >= pl.VCTMoveEndDate

  NOTES:

  mpk.Start_Date SmallDateTime

  mpk.End_DateSmallDateTime

  pl.VCTMoveStartDate DateTime

  pl.VCTMoveEndDate DateTime

  However, I need to add another filter to the query where:

  mpk.ActiveDate = pt.VCTContractDate

  1

  If no match is found, then where match is found

  within the last 7 days (TOP 1 in mpk.ActiveDate DESC date order - i.e. last date 1st)

  NOTES:

  mpk.ActiveDate SmallDateTime

  pt.VCTContractDateDateTime

  2

  If there isn't a matching mpr.AX_Location_Code found for a pl.VCTCollectionLocation then a 'NOT FOUND' is to be displayed in the output.

  Please can you help?

  Thanks in advance,

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  October 5, 2011 at 5:58 am

  #1390611

  Not enough information here.

  Please read the article linked in the first line of my signature. You will find out how to post effectively and get an answer quickly.

  -- Gianluca Sartori