TRUNCATE TABLE FNS_tbDFTR333
INSERT FNS_tbDFTR333

SELECT
CONVERT(VARCHAR(20) ,LoanReference ) AS LoanReference
,CONVERT(CHAR(3) ,ArrearsReasonCode) AS ArrearsReasonCode
,CONVERT(CHAR(1) ,Status ) AS Status
,CONVERT(INT ,ClientNumber ) AS ClientNumber
FROM
OPENQUERY(AS400,'SELECT * FROM ZAL.DFTR333 WHERE LOANREFERENCE LIKE ''74%''')




I want to extract from AS400 using open query all Clients that have a loanreference that
not Like '74%' and then took another loan from October Onwards.
How do I select the ClientNumber and group the loans by Loanreference.

Loanref ClientNumber
4533426 001
543626` 001
740000000000 001

5463637` 876
54252 876
65462 876
743214252222 876