>>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.