Try
where LT.cClient_Code = "1201" and LST.tEntered_Date > date() - 7
AND LST.tEntered_Date = (
SELECT MAX(tEntered_Date)
FROM load_stage_trans LST2
WHERE lst2.cClient_Account_Number = LT.cClient_Account_Number
AND LST2.cClient_Code = LT.cClient_Code)
)
>
>I have the following select
>
>select LT.cTrans_pk, LT.cPatients_fk, ;
> LT.iBatch_Number, LST.cStage_Trans_pk ;
> from load_trans LT inner join load_stage_trans LST ;
> on LT.cClient_Account_Number = LST.cClient_Account_Number and ;
> LT.cClient_Code = LST.cClient_Code ;
> where LT.cClient_Code = "1201" and LST.tEntered_Date > date() - 7 ;
> into cursor cursor_trans_stage nofilter ;
> order by LT.iBatch_Number
>
>Now I want the same select, but I want to use only records with maximum date from Load_Stage_Trans. In other words, I may have several records in LST, but I want just one of them with the latest date. How can I change my select statement?
--sb--
