Required result should be one record for every Act-No, with the latest transaction alone. i.e. record with max(day key) and if more than one transaction happens on the same day, the record with the highest time stamp is required.
Hence the result of the select should be as follows:-
,MAX(CONCAT(CAST(DIGITS(A.DAY-KEY) AS CHAR(10)),CAST(DIGITS(A.TIME) AS CHAR(12)))) AS DAY_KEY_COM
INNER JOIN GWACTD B
ON B.ACT-NO = AA.ACT-NO
AND B.DAY-KEY = CAST(SUBSTR(AA.DAY_KEY_COM,1,10) AS INT)
AND B.TIME = CAST(SUBSTR(AA.DAY_KEY_COM,11,12) AS DECIMAL(12,0))
Please let me know if there is any other way simpler way.
I don't know if it's SIMPLER ( or even if it's more EFFICIENT ), but I would probably code it thusly:
FROM GWACTD A
WHERE A.TIME =
FROM GWACTD B
WHERE B.ACT-NO = A.ACT-NO
AND B.DAY-KEY =
FROM GWACTD C
WHERE C.ACT-NO = A.ACT-NO)))