1 Reply Latest reply: Dec 7, 2016 11:26 AM by Sunny Talwar RSS

    3-step conditional Join

    gustav simberg

      Hi,

       

      I have three tables (in XML) where I need to make a conditional join.

       

      SalesTable

      DateItem ID
      2016-03-01A

       

      ItemTable

      ItemIDPriceListID
      AX

       

      PriceList

      PricelistIDValidFromPrice
      X2015-06-0120
      X

      2016-01-01

      25
      X2016-06-0130

       

      I now need to make a join to see what price the item had on 2016-03-01. something like

      use PriceList.Price where max(ValidFrom) < SalesTable.Date

       

      Is it possible to get help here?

      The goal is to get the relevant sales price attached to each sales line.

      ie

      DateItemIDPrice
      2016-03-01A25

      it needs to be outer joins, not all sales lines has an matching Item and not all Items has pricelistIDs

       

      much appreciated.