2 Replies Latest reply: Mar 10, 2014 11:14 AM by Fernando Martini RSS

    Join condition - date interval

    Fernando Martini

      hi everyone!

      Please help me with this question!!

       

      The table 'A' has the code and the sale date of an item. The table 'B' has all the input item in inventory and its cost.

      I need to return the average cost of an items between a date interval.

      How can I handle when i need to work with date interval?

      In the following example I know that the syntax is not correct. Just want to transmit the intention.

       

      [A]:

       

      LOAD

      [COD],

      [SALE DATE],

       

      FROM

      [MATERIAL.qvd]

      (qvd);

       

      LEFT JOIN

       

      LOAD

       

      [COD],

      [INPUT DATE],

      Sum([VL_TOTAL_ITEM_NF])/ Sum([QT_ITEM_ESTOQUE] * [QT_CONV_ESTOQUE_CONSUMO]) as [Average Cost]

      Resident [B]

      Where [INPUT DATE] <= [SALE DATE]

      and [INPUT DATE] >= [SALE DATE - 180];