Please help with getting two latest payments

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Please help with getting two latest payments

Author

Topic

mtl777
Yak Posting Veteran

63 Posts

Posted - 2012-06-13 : 12:24:09

Hey guys, I have a table of Customers and another table of Payments of those customers. The Customers table has RecordID, CustomerNo, CustomerName, Balance, etc. The Payments table has RecordID, CustomerNo, PaymentDate, PaymentAmount, etc. I am being asked to produce a report with the following format:

CustomerNo CustomerName Balance LastPayment1 LastPayment2

Where: CustomerNo, CustomerName, and Balance are obtained from the Customers table. LastPayment1 and LastPayment2 are the two last payment amounts obtained from the Payments table (LastPayment1 being more recent than LastPayment2). If either one or both are not found then they should be zero or NULL (whichever is more convenient for the query).

Could someone please help me with the query for this? I would preferably like something that does not need to use a cursor.

Your help would be most appreciated. Thanks in advance!

shilpash
Posting Yak Master

103 Posts

Posted - 2012-06-13 : 13:19:15

;WITH maxpayment
AS (SELECT
RecordID
,CustomerNo
,PaymentDate
,PaymentAmount
,ROW_NUMBER() OVER (PARTITION BY PaymentAmount ORDER BY PaymentDate DESC) AS t
FROM Payments
),

lastpayment1 AS (SELECT RecordID
,CustomerNo
,PaymentDate
,PaymentAmount FROM maxpayment WHERE t=1),

lastpayment2 AS (SELECT RecordID
,CustomerNo
,PaymentDate
,PaymentAmount FROM maxpayment WHERE t=2)

SELECT a.CustomerNo, a.CustomerName, a.Balance,ISNULL(lastpayment1.PaymentAmount,0),ISNULL(lastpayment2.PaymentAmount,0) from
Customers a
LEFT OUTER JOIN lastpayment1
ON a.recordid=lastpayment1.recordid
AND a.customerno=lastpayment1.customerno
LEFT OUTER JOIN lastpayment2
ON a.recordid=lastpayment2.recordid
AND a.customerno=lastpayment2.customerno

mtl777
Yak Posting Veteran

63 Posts

Posted - 2012-06-13 : 14:34:25

Hi Shilpash, thank you so much for your help! I wish we had SQL Server 2005 (at least) because the Row_Number() function is not available in SQL Server 2000 which is what we have. Would you be able to recode this in SQL Server 2000, please?

Also, I forgot to mention that Customers.RecordID and Payments.RecordID are not related at all. Customers.RecordID is the unique identity row ID for the Customers table, whereas Payments.RecordID is the unique identity row ID for the Payments table. The only relation between Customers and Payments is the CustomerNo field.

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 15:43:51

Can I chime in?


SELECT CustomerNo, CustomerName, Balance,
MAX(CASE WHEN Seq = 0 THEN PaymentAmount END) AS LastPayment1,
MAX(CASE WHEN Seq = 1 THEN PaymentAmount END) AS LastPayment2
FROM
(
SELECT c.CustomerNo,
c.CustomerName,
c.Balance,
p.PaymentAmount
COALESCE((SELECT COUNT(*)
           FROM Payments
           WHERE CustomerNo = p.CustomerNo
           AND PaymentDate > p.PaymentDate),0) AS Seq
FROM Customers c
LEFT JOIN Payments p
ON p.CustomerNo = c.CustomerNo
WHERE 2 >COALESCE((SELECT COUNT(*)
           FROM Payments
           WHERE CustomerNo = p.CustomerNo
           AND PaymentDate > p.PaymentDate),0)
)t
GROUP BY CustomerNo, CustomerName, Balance

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mtl777
Yak Posting Veteran

63 Posts

Posted - 2012-06-13 : 18:14:31

Hi Visakh16, thanks a lot! That's a very clever way to do it! My only concern is, will the query do the "SELECT COUNT(*) FROM Payments" twice for each CustomerNo and take long to execute because of that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 12:39:19

welcome

yep...it has to do twice as its correslated based on each paymentdate value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mtl777
Yak Posting Veteran

63 Posts

Posted - 2012-06-14 : 13:43:00

I was hoping it would "remember" the count from the first execution (the one in the SELECT list) and use that count for the next one (the one in the WHERE clause) since the two are exactly the same code...

COALESCE((SELECT COUNT(*)
FROM Payments
WHERE CustomerNo = p.CustomerNo
AND PaymentDate > p.PaymentDate),0)

SQL Server is not that smart to detect that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 15:41:37

nope it wont. we're not storing it somewhere but rather using it inline on both places

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources