>>This SQL returns what I want. Is it the most efficient way? (It assumes that we can not have two same statuses at the exact same time for one Trans):
>>
>>

>>text TO lcSQLStr TEXTMERGE noshow
>>SELECT cCode_Description as cStatus, ;
>>CAST(SUM(NVL(IIF(BETWEEN(Trans_Status_Codes.tAs_Of_Date,##START_DATE##,##END_DATE##),1,0),0)) AS I) ;
>>as iOriginal, ;
>>CAST(COUNT(Trans.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 ;
>>	INNER JOIN (SELECT  ;
>>	cTrans_pk, MAX(tAs_of_Date) as tMax_Date ;
>>	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 = <<m.tiBatch_Number>> ;
>>	AND VisCodes.cCode_Description LIKE "NAME SEARCH %" ;
>>	group BY cTrans_pk) AS Max_Status ;
>>	ON Trans_Status_Codes.cTrans_fk = Max_Status.cTrans_pk and ;
>>	Trans_Status_Codes.tAs_of_Date = Max_Status.tMax_Date ;
>>	WHERE Trans.iBatch_Number = <<m.tiBatch_Number>> ;
>>	AND VisCodes.cCode_Description LIKE "NAME SEARCH %" ;
>>	GROUP BY 1					
>>ENDTEXT
>>lcSQLStr = strtran(m.lcSQLStr, '##START_DATE##', m.lcStart_Date)
>>lcSQLStr = strtran(m.lcSQLStr, '##END_DATE##', m.lcEnd_Date)

>
>

>text TO lcSQLStr TEXTMERGE noshow
>SELECT cCode_Description as cStatus ;
>,CAST(SUM(IIF(BETWEEN(Trans_Status_Codes.tAs_Of_Date,##START_DATE##,##END_DATE##),1,0)) AS I) as iOriginal ;
>,COUNT(*) as iCurrent ;
>	FROM
>(SELECT  cTrans_fk , MAX(tAs_of_Date) as tMax_Date ;
>			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 = <<m.tiBatch_Number>> AND VisCodes.cCode_Description LIKE "NAME SEARCH %" ;
>			group BY cTrans_fk ) AS Max_Status ;
>	INNER JOIN MMVisCollect!Trans_Status_Codes	;
>		ON Max_Status.cTrans_fk = Trans_Status_Codes.cTrans_fk and Trans_Status_Codes.tAs_of_Date = Max_Status.tMax_Date ;
>	INNER JOIN MMVisCollect!VisCodes 		ON Trans_Status_Codes.cStatus_Codes_fk = VisCodes.cVisCodes_pk ;
>	GROUP BY 1					
>ENDTEXT
>lcSQLStr = strtran(m.lcSQLStr, '##START_DATE##', m.lcStart_Date)
>lcSQLStr = strtran(m.lcSQLStr, '##END_DATE##', m.lcEnd_Date)
>

Thanks. looks a little bit simpler than mine.