SQL Help Needed

[MAB]

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

[Uri Dimant]

mab
Unfortunate I cannot test it

---Make view and the join the view with SELECT statement
CREATE VIEW MY_VIEW
AS
SELECT CUSTOMERID,AMOU NT AS AMOUNT
FROM PAYMENTS
INNER JOIN
(
SELECT CUSTOMERID,AMOU NT ,MAX(DATE) AS DATE FROM PAYMENTS
GROUP BY AMOUNT,CUSTOMER ID
) P1
ON P1.CUSTOMERID=P AYMENTS.CUSTOME RID
---------------------------
SELECT SUM(AMOUNT) FROM MY_VIEW

"MAB" <fk************ *****@yahoo.com > wrote in message
news:bj******** ****@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

[Erland Sommarskog]

MAB (fk************ *****@yahoo.com ) writes:

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.

This solution is not tested, as you did not provide any sample data:

SELECT SUM(p3.amount)
FROM Payments p3
JOIN (SELECT paymentid = MAX(p2.paymenti d)
FROM Payments p2
JOIN (SELECT p1.customerid, mostrecent = MAX(p1.date)
FROM Payments p1
WHERE p1.date <= '20030301'
GROUP BY p1.customerid) AS p1
ON p1.customerid = p2.customerid
AND p1.mostrecent = p2.date) AS p2
ON p3.paymentid = p2.paymentid

This solution is for SQL Server only. I don't know Access, so I can't
help with that.

As for performance, this is likely to be a case of finding the best
indexes. Clustered on (date, customerid) and nonclustered in (paymentid)
maybe.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John Gilson]

"MAB" <fk************ *****@yahoo.com > wrote in message
news:bj******** ****@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_TIMESTA MP

CREATE FUNCTION LatestPaymentsA llCustomers
(@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 LatestPaymentsA llCustomers(DEF AULT)

-- For latest payments as of a particular date, e.g., 20030301
SELECT total
FROM LatestPaymentsA llCustomers('20 030301')

Regards,
jag