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
