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??
