Here's the Problem Again With Sample Data

[MAB]

I want the sum of the last payments (amount) for all customers. The last
payment is with one with most recent date. And if there are more than one
payment on the most recent date then the one with the higher paymentid is
the last payment. for example in the given data the insert statement that
starts with capital I is the last payment of that customer. The correct
answer should be 2100 as given below. both queries by Erland and Anith give
the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
both queries since right now I want current sum (not till some date). So
what should be the right query.

Thanks again for the help.

create table payments (
paymentid int,
customerid int,
amount int,
date datetime
)

insert payments values (1, 1, 100, '1/1/03')
insert payments values (2, 1, 200, '2/28/03')
Insert payments values (3, 1, 500, '5/15/03')

insert payments values (4, 2, 400, '1/16/03')
insert payments values (9, 2, 800, '4/30/03')
insert payments values (5, 2, 200, '6/15/03')
Insert payments values (6, 2, 900, '6/15/03')

insert payments values (7, 3, 700, '3/1/03')
insert payments values (10,3, 300, '7/10/03')
Insert payments values (8, 3, 600, '9/1/03')

insert payments values (11,4, 300, '8/1/03')
insert payments values (12,4, 400, '9/10/03')
Insert payments values (13,4, 100, '9/10/03')
customerid lastpayment amount

1 3 (on 5/15/03) 500
2 6 (on 6/15/03) 900
3 8 (on 9/1/03) 600
4 13 (on 9/10/03) 100

========
Result => 2100

[oj]

select customerid,paymentid,[date],amount
from payments p1
where paymentid=(select top 1 paymentid from payments p2 where
p2.customerid=p1.customerid order by [date] desc, paymentid desc)
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"MAB" <ds******************@yahoo.com> wrote in message
news:bj************@ID-31123.news.uni-berlin.de...

I want the sum of the last payments (amount) for all customers. The last
payment is with one with most recent date. And if there are more than one
payment on the most recent date then the one with the higher paymentid is
the last payment. for example in the given data the insert statement that
starts with capital I is the last payment of that customer. The correct
answer should be 2100 as given below. both queries by Erland and Anith give the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
both queries since right now I want current sum (not till some date). So
what should be the right query.

Thanks again for the help.

create table payments (
paymentid int,
customerid int,
amount int,
date datetime
)

insert payments values (1, 1, 100, '1/1/03')
insert payments values (2, 1, 200, '2/28/03')
Insert payments values (3, 1, 500, '5/15/03')

insert payments values (4, 2, 400, '1/16/03')
insert payments values (9, 2, 800, '4/30/03')
insert payments values (5, 2, 200, '6/15/03')
Insert payments values (6, 2, 900, '6/15/03')

insert payments values (7, 3, 700, '3/1/03')
insert payments values (10,3, 300, '7/10/03')
Insert payments values (8, 3, 600, '9/1/03')

insert payments values (11,4, 300, '8/1/03')
insert payments values (12,4, 400, '9/10/03')
Insert payments values (13,4, 100, '9/10/03')
customerid lastpayment amount

1 3 (on 5/15/03) 500
2 6 (on 6/15/03) 900
3 8 (on 9/1/03) 600
4 13 (on 9/10/03) 100

========
Result => 2100

[MAB]

Thanks! this works. However its too slow to run on the actual table with
thousands of rows but i've managed to eliminate most of the rows by creating
a temporary table and then I run this query on the temporary table.

"oj" <no**********@home.com> wrote in message
news:uq**************@TK2MSFTNGP12.phx.gbl...

select customerid,paymentid,[date],amount
from payments p1
where paymentid=(select top 1 paymentid from payments p2 where
p2.customerid=p1.customerid order by [date] desc, paymentid desc)
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"MAB" <ds******************@yahoo.com> wrote in message
news:bj************@ID-31123.news.uni-berlin.de...

I want the sum of the last payments (amount) for all customers. The last
payment is with one with most recent date. And if there are more than one payment on the most recent date then the one with the higher paymentid is the last payment. for example in the given data the insert statement that starts with capital I is the last payment of that customer. The correct
answer should be 2100 as given below. both queries by Erland and Anith

give

the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from both queries since right now I want current sum (not till some date). So
what should be the right query.

Thanks again for the help.

create table payments (
paymentid int,
customerid int,
amount int,
date datetime
)

insert payments values (1, 1, 100, '1/1/03')
insert payments values (2, 1, 200, '2/28/03')
Insert payments values (3, 1, 500, '5/15/03')

insert payments values (4, 2, 400, '1/16/03')
insert payments values (9, 2, 800, '4/30/03')
insert payments values (5, 2, 200, '6/15/03')
Insert payments values (6, 2, 900, '6/15/03')

insert payments values (7, 3, 700, '3/1/03')
insert payments values (10,3, 300, '7/10/03')
Insert payments values (8, 3, 600, '9/1/03')

insert payments values (11,4, 300, '8/1/03')
insert payments values (12,4, 400, '9/10/03')
Insert payments values (13,4, 100, '9/10/03')
customerid lastpayment amount

1 3 (on 5/15/03) 500
2 6 (on 6/15/03) 900
3 8 (on 9/1/03) 600
4 13 (on 9/10/03) 100

========
Result => 2100

[Erland Sommarskog]

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

Thanks! this works. However its too slow to run on the actual table with
thousands of rows but i've managed to eliminate most of the rows by
creating a temporary table and then I run this query on the temporary
table.

You might need to review your indexes. It is difficult to test
performance on the small sample, but I would try somthing like:

CREATE CLUSTERED INDEX payments_index ON
payments(customerid, date DESC, paymentid DESC)

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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

[oj]

As Erland has suggested you should visit your indexing strategy. There is a
cost for creating the temp table and inserting data into it. With proper
index, this should be a breeze.

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"MAB" <fk*****************@yahoo.com> wrote in message
news:bj************@ID-31123.news.uni-berlin.de...

Thanks! this works. However its too slow to run on the actual table with
thousands of rows but i've managed to eliminate most of the rows by creating a temporary table and then I run this query on the temporary table.

"oj" <no**********@home.com> wrote in message
news:uq**************@TK2MSFTNGP12.phx.gbl...

select customerid,paymentid,[date],amount
from payments p1
where paymentid=(select top 1 paymentid from payments p2 where
p2.customerid=p1.customerid order by [date] desc, paymentid desc)
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"MAB" <ds******************@yahoo.com> wrote in message
news:bj************@ID-31123.news.uni-berlin.de...

I want the sum of the last payments (amount) for all customers. The last payment is with one with most recent date. And if there are more than one payment on the most recent date then the one with the higher paymentid is the last payment. for example in the given data the insert statement that starts with capital I is the last payment of that customer. The correct answer should be 2100 as given below. both queries by Erland and Anith

give

the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from both queries since right now I want current sum (not till some date). So what should be the right query.

Thanks again for the help.

create table payments (
paymentid int,
customerid int,
amount int,
date datetime
)

insert payments values (1, 1, 100, '1/1/03')
insert payments values (2, 1, 200, '2/28/03')
Insert payments values (3, 1, 500, '5/15/03')

insert payments values (4, 2, 400, '1/16/03')
insert payments values (9, 2, 800, '4/30/03')
insert payments values (5, 2, 200, '6/15/03')
Insert payments values (6, 2, 900, '6/15/03')

insert payments values (7, 3, 700, '3/1/03')
insert payments values (10,3, 300, '7/10/03')
Insert payments values (8, 3, 600, '9/1/03')

insert payments values (11,4, 300, '8/1/03')
insert payments values (12,4, 400, '9/10/03')
Insert payments values (13,4, 100, '9/10/03')
customerid lastpayment amount

1 3 (on 5/15/03) 500
2 6 (on 6/15/03) 900
3 8 (on 9/1/03) 600
4 13 (on 9/10/03) 100

========
Result => 2100