SQL JOIN SYNTAX

[MAB]

Erland Sommarskog <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...

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.paymentid)
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.

Thanks. This looks fascinating. It looks correct too (although I havent
fully verified that). From this my next question
Is it possible to write your query in older join syntax like

FROM Payments p1, Payments p2
WHERE p1.customerid = p2.customerid etc.

Or is it that the newer syntax is superior such that you can do things with
it that you cant do with the older one?
I cant see how to write such a query the older way because you have only one
SELECT Clause there

Many thanks again

[Anith Sen]

Try:

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

--
- Anith
( Please reply to newsgroups only )

[MAB]

SELECT SUM(p3.amount)
FROM Payments p3
JOIN (SELECT paymentid = MAX(p2.paymentid)
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.

Unfortunately this doesnt seem to work :(
It only brings the amount of 1 payment. So if you replace SUM(p3.amount)
with something like

p3.Paymentid, p3.customerid, p3.amount you only get 1 row which is obviously
incorrect because I need one paymentid ( the last one ) for each customer
and SUM(p3.amount) should get the total of all those records.
Can you fix this?

[Anith Sen]

>> Can you fix this? <<

Not entirely, since I am not familiar with your requirements. All I have
done is simply changed the query with older syntax. If you need an exact &
accurate working solution, you'll have to provide the table DDLs (CREATE
TABLE statements ), sample data (INSERT statements) and expected results
along with a brief explanation of the required logic.

--
- Anith
( Please reply to newsgroups only )

[Erland Sommarskog]

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

Unfortunately this doesnt seem to work :(
It only brings the amount of 1 payment. So if you replace SUM(p3.amount)
with something like

p3.Paymentid, p3.customerid, p3.amount you only get 1 row which is
obviously incorrect because I need one paymentid ( the last one ) for
each customer and SUM(p3.amount) should get the total of all those
records.
Can you fix this?

Maybe. As I pointed out in my original posting, the solution was
untested as you did not provide sample data and the expected result
from the sample.

So please post the following:

o CREATE TABLE statement for your table (which you already have, but
would be convenient with having all in one post.)
o INSERT statements with sample data.
o The expected result from the sample.

It would also be helpful if you repeated the problem as such, since you
did not post your reply as a followup to my post. By posting a followup,
it's easy for me to go back and review previous articles in the thread.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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