Hi everybody,

I'm trying to obtain statistics for the batch of records. I'd like to find out how many records were sent to NAME SEARCH.

Here is the draft:
text TO lcSQLStr TEXTMERGE noshow
SELECT cCode_Description as cStatus, ;
CAST(SUM(IIF(BETWEEN(tAs_Of_Date,##START_DATE##,##END_DATE##),1,0)) AS I) ;
as iOriginal, ;
CAST(COUNT(cTrans_pk) AS I) as iCurrent ;
FROM MMVisCollect!Trans ;
INNER JOIN MMVisCollect!Trans_Status_Codes ;
ON Trans.cTrans_pk = Trans_Status_Codes.cTrans_fk ;
INNER JOIN MMVisCollect!VisCodes ;
ON Trans_Status_Codes.cStatus_Codes_fk = VisCodes.cVisCodes_pk ;
WHERE Trans.iBatch_Number = <> ;
AND VisCodes.cCode_Description LIKE "NAME SEARCH %" ;
GROUP BY Trans.cTrans_pk
ENDTEXT

Problem:
1) One record may have multiple status codes with few NAME SEARCH statuses (the whole description would be different). In this case I'd like to get the latest NAME SEARCH status (tAs_Of_Date field) and count this record as one.

2) Some NAME Searches could be performed after the imported date. I want to count these records in iCurrent, but not in the iOriginal with the same idea - if there are multiple NAME SEARCH statuses I'd like to take the latest.

Could you please help me out?

Thanks in advance.