3 Replies Latest reply: Nov 5, 2011 2:29 PM by Alexander Lampart RSS

    sum with left join and date

    Alexander Lampart

      Dear guys,

       

      very simple question I thought. Here's what I want to do.

       

      I have a "LedgerEntry" table which looks like this:

       

      Item No.Posting DateQuantity
      081501.01.1110
      081502.01.115
      081504.01.1110


      I want to make a new table "InventoryEntry" in the script that represents the following

       

      Posting DateItem No.Inventory
      01.01.11081510
      02.01.11081515
      03.01.11081515
      04.01.11081525


      Inventory is the sum of all ledger entries until that date.

       

      I already managed to create a table that contains an entry for each "Posting Date" and each "Item No.". Now I want to join the sum (Quantity) in that table as Inventory. I thought it would be something like this:

       

      LEFT JOIN (InventoryEntry) LOAD

        sum(Quantity) AS Inventory 

      resident LedgerEntry WHERE [Posting Date] <= InventoryEntry.[Posting Date]

       

      Haha. Nice try. I cannot make a reference between the two tables after the WHERE.

      So I tried to gather some wisdom in the forum and found something about the INTERVALMATCH.

       

      I added the fields "FromDate" and "ToDate" to the InventoryEntry Table. It now looks like this:

       

      Posting DateItem NoInventoryFromDate
      ToDate
      04.01.11081501.01.1104.01.11

       

      But I can't get the the INTERVALMATCH to work. Is INTERVALMATCH the right way to solve this problem? What other options do I have`?

       

      Thanks in advance

       

      Regards

       

      Alex