I have a table
Create Table Payments {
paymentid int,
customerid int,
amount int,
date datetime
}
What I want is the sum of the amounts of the last payments of all customers.
Now the last payment of a customer is not necessarily the one with the
highest paymentid for that customer BUT it is the one with the highest
paymentid on the MOST RECENT date. We dont keep the time part just the date
so if there are more than 1 payments of a customer on a date ( and there are
many such cases ) only then the paymentid decides which is the last payment.
Further the last payment may be the last as of today but I may want to find
the sum of all the last payments upto say March 1, 2003
or any date. My own solution is too slow even it is correct.
SELECT SUM( AMOUNT )
FROM PAYMENTS AS P1
WHERE PAYMENTID =
( SELECT MAX( PAYMENTID ) FROM PAYMENTS AS P2 WHERE P1.CUSTOMERID =
P2.CUSTOMERID AND DATE =
( SELECT MAX(DATE) FROM PAYMENS AS P3 WHERE P3.CUSTOMERID = P2.CUSTOMERID
AND DATE < #9/8/03# ))
What would be the most efficient solution to this.
Both in SQL Server and in Access 2000
thx in advance