0 Replies Latest reply: Feb 25, 2010 3:38 AM by Anita Fuchten RSS

    sum in the load statement ... need some help

    Anita Fuchten

      Hi all,

      In the LOAD statement I want to sum a RepeatCounter for each row looking back 6 months ...

      For each row I need a sum of the following:

      The Orderdate of the lookup records should be between Orderdate of the current row and Orderdate - 6 months
      and the SerialNumber should also be the same ... (this indicates there is a repeated repair which we want to count)

       

      I already have some counters which I created, but this is the last counter needed ... and I can't find a good solution.

       

      RepeatRepair:
      LOAD *,
      IF (
      (SerialNumber = previous (SerialNumber)) and
      Serviceordernr <> previous(Serviceordernr) and
      (addmonths(Orderdate, -6) < previous (Orderdate)),
      1,0) as RepeatCounter
      ;
      LOAD
      Serviceordernr,
      SerialNumber,
      Orderdate
      RESIDENT RepeatRepairTemp
      ORDER BY SerialNumber, Orderdate, Serviceordernr;


      JOIN
      LOAD *,
      IF (
      (SerialNumber = previous (SerialNumber)) and
      Serviceordernr <> previous(Serviceordernr) and
      (addmonths(Orderdate , -6) < previous (Orderdate )),
      1,0) as RepeatCounter_repeatedSO
      ;
      LOAD
      Serviceordernr,
      SerialNumber,
      Orderdatum
      RESIDENT RepeatRepairTemp
      ORDER BY SerialNumber, Orderdate DESC, Serviceordernr DESC;


       

      Anyone has suggestions on how to do this?? Is there a way of using a while loop??

      thx

      Anita