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]

Re: SQL Help Needed

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" <fkdfjdierkjfla fdafa@yahoo.com > wrote in message
news:bjfedr$i39 gs$1@ID-31123.news.uni-berlin.de...[color=blue]
> 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[/color]
customers.[color=blue]
> 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[/color]
date[color=blue]
> so if there are more than 1 payments of a customer on a date ( and there[/color]
are[color=blue]
> many such cases ) only then the paymentid decides which is the last[/color]
payment.[color=blue]
> Further the last payment may be the last as of today but I may want to[/color]
find[color=blue]
> 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 =[/color]
P2.CUSTOMERID[color=blue]
> 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
>
>
>
>
>
>
>
>
>[/color]

[Erland Sommarskog]

Re: SQL Help Needed

MAB (fkdfjdierkjfla fdafa@yahoo.com ) writes:[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[John Gilson]

Re: SQL Help Needed

"MAB" <fkdfjdierkjfla fdafa@yahoo.com > wrote in message
news:bjfedr$i39 gs$1@ID-31123.news.uni-berlin.de...[color=blue]
> 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[/color]

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