5 Replies Latest reply: Mar 2, 2011 3:17 PM by alejandro hernández RSS

    How to get date interval overlapping

      I have a table like this:

       

       



      prova:
      LOAD * INLINE [
      mac_, from_, to_, value_
      m1, 1/12/2005, 31/1/2006, 1000
      m1, 1/12/2006, 31/1/2007, 2000
      m2, 1/12/2005, 31/1/2006, 1500
      ];



       

      I would like to know the fraction of each interval (from_->to_) in each year in order to calculate the total value_ amount for each year. In the example for mac_ = m1 and year = 2006: 1000*1/12 + 2000 * 1/12.

      I think I can find the solution by using several nested if, but is there any ready-made function in qv which returns the overlapping of two time intervals? (i.e.: 1/1/2006-31/12/2006 vs 1/12/2005-31/1/2006)

      Thanks in advance for your help