>>>This is one of the possible variations of this query.
>>
>>Packed solution:
>>
>>select Cust.*, LO.*LastDate, cast(RIGHT(LO.DateAmount,100) as N(12,4)) as Amount from Customers Cust ;
>>
>>INNER JOIN (select CustNo, max(Date) as LastDate, ;
>>max(cast(date as C(10)) + cast(Amount as C(100))) as DateAmount ;
>>from Orders group by CustNo) LO on Cust.CustNo = LO.CustNo
>>
>>
>>Should perform better than other alternatives.
>
>
>
>But where you sum the whole amount for the order?
>One order can many rows, not just one :-)
I assume that the Orders table contains OrderID, OrderDate, TotalAmount and OrderDetails (not listed here in the requirements) has details of the order.
Don should clarify the structure of the tables.
Also, how will you stamp each order record with the same date - you can not use datetime then or you need to use the same datetime for all order records for that order.
If it's not broken, fix it until it is.
My Blog