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)