2 Replies Latest reply on Feb 13, 2018 1:45 PM by Brian Delehanty

    Join where Date falls between 2 dates

    steve cassarlaudi

      Hi Guys,

       

      I've run into a bit of a brick wall and maybe someone could help me out,

       

      I have a table with information for sales made to a client:

      Sales Rep
      Date of SaleItem CodeBuyerCost
      A2018/01/12MGA442ZZ1.3
      B2017/12/12MGA442XX1.3
      C2018/02/02MGA442VV1.3

       

      Now i'd like to join the above table with a table for adjustments, basically the price changes overtime and the excel file i have is in the following format

      Item CodeChange DateCost
      MGA4422018/02/021.3
      MGA4422018/01/010.95
      MGA4422017/01/010.55

       

      What i'd like to do is create a join between the two tables so that i can get the appropriate new cost for the item instead of it always getting the latest data,

       

      I've already tried using 'DATE >= Change Date' but to no avail as it gives multiple rows for each line.

       

      If you need a workbook let me know and ill create one asap, currently away from computer but will get one if needs be !

        • 1. Re: Join where Date falls between 2 dates
          Wilson Po

          Hi Steve,

          You might want to union the data instead of joining it - a join would append additional columns to the data set rather than adding the new price changes as just additional records.  This does depend on what you are trying to visualize between the two tables. I'm also not entirely clear whether the columns correspond to each other exactly (does the "Cost" in both tables mean the same thing?), but it seems likely that this might be what you want to do. The core idea is combining both tables along the date, item cost, and cost; rather than having unique date fields that represent "Change Date" and "Date of Sale"  both are combined to the same column in a disjointed table:

           

          Event TypeSales RepDateItem CodeBuyerCost
          Price ChangeN/A2017/01/01MGA442N/A0.55
          SaleB2017/12/12MGA442XX1.3
          Price ChangeN/A2018/01/01MGA442N/A0.95
          SaleA2018/01/12MGA442ZZ1.3
          Price ChangeN/A2018/02/02MGA442N/A1.3
          SaleC2018/02/02MGA442VV1.3

           

          Because the tables do not have the same columns originally, custom SQL can help structure this format across the two tables.  For Excel, you will need to connect to the file with the Excel legacy connector to be able to connect to data via custom SQL:

           

          SELECT

          "Sale" AS [Event Type],

          [Sales].[Sales Rep] AS [Sales Rep],

          [Sales].[Date of Sale] AS [Date],

          [Sales].[Item Code] AS [Item Code],

          [Sales].[Buyer] AS [Buyer],

          [Sales].[Cost] AS [Cost]

          FROM [Sales]

          UNION

          SELECT

          "Price Change" AS [Event Type],

          "N/A" AS [Sales Rep],

          [Price Change].[Change Date] AS [Date],

          [Price Change].[Item Code] AS [Item Code],

          "N/A" AS [Buyer],

          [Price Change].[Cost] AS [Cost]

          FROM [Price Change]

           

          You may need to adjust depending on how Excel reads the table names, but this type of query will get you an output table as described above.  If you are thinking of a different structure of the data , let me know and we can see what we can recommend. 

          • 2. Re: Join where Date falls between 2 dates
            Brian Delehanty

            I had a similar issue that I solved in SQL Server and I imagine could be done in CustomSQL; maybe this will work for you or at least get you close. I had a series of milestones, but wanted to fill in the gap between the milestones with dates so I could answer the question, "What milestone was this order at on X date?". There are two components you need: Next Date and a JOIN Clause.

             

            For the Next Date, you can use the LEAD function in SQL on the bottom table to show when the adjustments are active:

             

            LEAD([Change Date],1) OVER (PARTITION BY [Item Code] ORDER BY [Change Date] asc) as [Next Change Date].

             

            This will give you the next change date and a null for the current for each Item Code.


            When you join, you just plot out the scenarios and say

             

            ON a.[Date of Sale]>=b.[Change Date] AND a.[Date of Sale] <= IIF(b.[Next Change Date] IS NULL, cast(getdate() as date), b.[Next Change Date]).

             

            This should basically say, if you sales date is greater than the change date, but less than the next change date, use that adjustment. If there is not a new change date, the current change date, then you would use today's date as the upper threshold. Now you just are asking whether a date is between two dates on a single record.