1 Reply Latest reply on Aug 16, 2018 11:53 AM by Patrick Van Der Hyde

    SQL joining nearest last date

    Zelig Chan

      Hi All,


      I am using Tableau Desktop trying to join mySQL tables.


      I am facing a problem now in joining three following tables

      - product (which shows product.id and all other product details)

      - inventory adjustment log (which logs the quantity change whenever a product is sold or inbounded) 10million rows

      - product cost log (which logs the product cost change whenever a product is inbounded) 1 million rows


      The problem now is that I an now trying to prepare finance reports in calculating the stock value (Q*P) as at a certain time, so basically i need to do the following:

      - first "product table" leftjoin "inventory adjustment log" on product.id


      i also want to leftjoin the product cost log on product.id and cost_adj_date as well so that i can get just the latest cost for the product as well when the qty changed,

      however the problem is that how can i join inv_adj_date and cost_adj_date as they are not direct matches, if the inv_adj_date is 1/1/2017 3:21 then i should find the latest cost at that time, (which in the example should be 1/1/2017 1:21 for product 1001 apple)


      Can anyone help me on this? Many Thanks!


        • 1. Re: SQL joining nearest last date
          Patrick Van Der Hyde

          Hello Zelig,


          How are you calculating this today in existing applications?  You will need to compare the max time/date of the value for the product id before the date time of the row in question in the inventory adj log.  Doing this in Tableau directly with a join won't work because there is not a field to link on other than product id.  I am thinking that if you had a way to create a log table of the changes for product cost log down to the smallest interval required (maybe every 15 min) in such a way that you have entries in a table for every 15 minute period of time or worse case to every minute with the lastest value/cost then this could be linked to from the primary table on time and product. id accurately.  The size of the tables (this indicates more than 1 million rows) would seem to make this not a workable solution.   How often is the date/time adjustment changing and can you be safe in rounding to some value in both tables so they could be matched?