Looking for how to return the previous payment

I use the following sql in a view to return the last payment date and amount made by clients. What I need is a way to return the payment date and amount for the payment prior to the last one.

Any help is appreciated very much,

SELECT dbo.tblPaymentReceipts.Client_ID, dbo.tblPaymentReceipts.PaymentDateAS LastPayDate,SUM(dbo.tblPaymentReceipts.AmountPaid)AS LastPayAmt
FROM dbo.tblPaymentReceiptsINNERJOIN
(SELECT Client_ID,MAX(PaymentDate)AS LastPayDate
FROM dbo.tblPaymentReceiptsAS tblPaymentReceipts_1
GROUP BY Client_ID)AS AON dbo.tblPaymentReceipts.Client_ID = A.Client_IDAND dbo.tblPaymentReceipts.PaymentDate = A.LastPayDate
GROUP BY dbo.tblPaymentReceipts.Client_ID, dbo.tblPaymentReceipts.PaymentDate

You could try something like this...

SELECT
X.ClientID,
P.PaymentDate AS ThisPaymentDate,
P.Amount AS ThisPaymentAmount,
L.PaymentDate AS LastPaymentDate,
L.Amount AS LastPaymentAmount
FROM
(
SELECT
ClientID,
MAX(PaymentDate) AS PaymentDate
FROM tblPaymentReceipts
GROUP BY ClientID
) AS X
LEFT JOIN
(
SELECT
R.ClientID,
Max(R.PaymentDate) AS PaymentDate
FROM
(SELECT ClientID, MAX(PaymentDate) AS PaymentDate FROM tblPaymentReceipts GROUP BY ClientID) AS Z
LEFT JOIN tblPaymentReceipts R
ON Z.ClientID = R.ClientID
AND R.PaymentDate < Z.PaymentDate
GROUP BY R.ClientID
) AS Y
ON X.ClientID = Y.ClientID

LEFT JOIN tblPaymentReceipts P
ON X.ClientID = P.ClientID
AND X.PaymentDate = P.PaymentDate

LEFT JOIN tblPaymentReceipts L
ON Y.ClientID = L.ClientID
AND Y.PaymentDate = L.PaymentDate

This most likely isn't the most efficient query but its a starting point for you...

John

|||

--SQL Server 2005

SELECT Client_ID, PaymentDate, AmountPaidFROM(SELECT Client_ID, PaymentDate, AmountPaid, ROW_Number()OVER(PARTITIONBY Client_IDORDERBY PaymentDateDESC)as RowNum

FROM tblPaymentReceipts)AS t

WHERE RowNum=2--RowNum=1 for latest payment

-- If you are not using SQL Server 2005:

SELECT Client_ID, PaymentDate, AmountPaidFROM(SELECT Client_ID, PaymentDate, AmountPaid,

(SELECTCOUNT(*)FROM tblPaymentReceiptsAS a

WHERE(PaymentDate>= b.PaymentDate)AND(Client_ID= b.Client_ID))AS RowNum

FROM tblPaymentReceiptsAS b

)AS t

WHERE RowNum= 2--RowNum=1 for latest payment

|||

These options are much faster to run, however, they are not supoorted as views and I need this to work in a view. the OVER is not supported in SQL Server Express and/or Views.

Dave's option is not as fast but will work in a view.

Thank you both very much,

|||

--SQL Server 2005

CREATEVIEW [dbo].[View_2]

AS

SELECT Client_ID, PaymentDate, AmountPaid

FROM(SELECT Client_ID, PaymentDate, AmountPaid, ROW_Number()OVER(PARTITIONBY Client_ID

ORDERBY PaymentDateDESC)AS RowNum

FROM tblPaymentReceipts)AS t

WHERE RowNum= 2

It shows the error message of OVER not supported at the IDE window, but the view will run correctly.

|||

perhaps I misunderstood the problem, but I thought he was trying to return BOTH the current payment and the previous payment info.?

|||

You are right.

As long as he can work along these logics, it should be straight to get customized results.