[quoted text, click to view] "MAB" <fkdfjdierkjflafdafa@yahoo.com> wrote in message
news:bjfedr$i39gs$1@ID-31123.news.uni-berlin.de...
> 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
With SQL Server 2000, one can use a UDF.
CREATE VIEW Now (date_time)
AS
SELECT CURRENT_TIMESTAMP
CREATE FUNCTION LatestPaymentsAllCustomers
(@d DATETIME = NULL)
RETURNS TABLE
AS
RETURN(
SELECT SUM(P2.amount) AS total
FROM (SELECT customerid, MAX(date) AS latest
FROM Payments
WHERE date <= COALESCE(@d, (SELECT date_time FROM Now))
GROUP BY customerid) AS P1
INNER JOIN
Payments AS P2
ON P1.customerid = P2.customerid AND
P1.latest = P2.date AND
NOT EXISTS (SELECT * FROM Payments
WHERE customerid = P1.customerid AND
date = P1.latest AND
paymentid > P2.paymentid)
)
-- For latest payments as of now
SELECT total
FROM LatestPaymentsAllCustomers(DEFAULT)
-- For latest payments as of a particular date, e.g., 20030301
SELECT total
FROM LatestPaymentsAllCustomers('20030301')
Regards,
jag
