How can I get the last and previous payments

[3587ch]

I have a tblPayments and I need to get a list of each clients LAST AND PREVIOUS payment amount and date of payment. I have paymentID, ClientID, PaymentDate, and PaymentAmount fields in the table.

Can anyone show me how this can be done?

Thank you very much.

 

[GKChesterton]

SELECT TOP 2 paymentID, ClientID, PaymentDate, PaymentAmount 
FROM paymentTable 
WHERE ClientID=[value] 
ORDER BY PaymentDate DESC

... that will get you the two relevant records, for a start

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[3587ch]

That is a good start, but I need to show the last and previous amounts and dates in one row for the clients.

Something like:
Client_ID,LastPmtDate,LastPmtAmt,PrevPmtDate,PrevPmtAmt

How can I do this?

 

[GKChesterton]

Here is one approach in rough/pseudo form. The first query is nested in A, then used again and nested in B. Then A and B are nested in another query for final presentation.

Probably the client index will be used more than cited here ... working these to final form is well beyond me this Friday moment.

Query TwoMostRecent:

SELECT TOP 2 paymentID, ClientID, PaymentDate, PaymentAmount
FROM paymentTable
WHERE ClientID=[value]
ORDER BY PaymentDate DESC

Query A:

SELECT TOP 1 PaymentDate, PaymentAmount
FROM TwoMostRecent
ORDER BY PaymentDate DESC

Query B:

SELECT TOP 1 PaymentDate, PaymentAmount
FROM TwoMostRecent
ORDER BY PaymentDate ASC

Query Final:

SELECT A.PaymentDate, A.PaymentAmount, B.PaymentDate, B.PaymentAmount
FROM A JOIN B

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[TheAceMan1]

How are ya 3587ch . . .

Can be done with a sum query using an IN clause, but I need the following info:
[ol][li]The [blue]actual name[/blue] of your [blue]clients table[/blue] that [blue]ClientID[/blue] belongs to. ClientID in this table should be the primary key.[/li]
[li]The [blue]data type[/blue] of ClientID (numeric or text).[/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[3587ch]

TheAceMan1,

The client table is tblClients and the ClientID is an Autonumber data type.

I very much appreciate your help!

 

[TheAceMan1]

3587ch . . .

As an example, the following is based on a client table name of [blue]tblClients[/blue] and a ClientID data type of [blue]numeric[/blue] ...
[ol][li]Copy the following SQL:

[blue]SELECT tblClients.ClientID, tblPayments.PaymentDate, tblPayments.PaymentAmount
FROM tblClients INNER JOIN tblPayments ON tblClients.ClientID = tblPayments.ClientID
WHERE (((tblPayments.PaymentDate) In (SELECT TOP 2 tblPayments.PaymentDate FROM tblPayments WHERE tblClients.ClientID=tblPayments.ClientID Order By tblPayments.PaymentDate DESC)))
ORDER BY tblClients.ClientID, tblPayments.PaymentDate DESC;[/blue]

[/li]
[li]Open a new query in design view (do not select any tables!).[/li]
[li]Select the toolbar button with [blue]SQL[/blue] on it.[/li]
[li]Paste what you copied over [blue]SELECT;[/blue][/li]
[li]Save the query.[/li]
[li][blue]Perform your testing![/blue][/li][/ol]
If the I got the name of the client table correct ([blue]tblClients[/blue]) it should work. The SQL is pretested.

[blue]Your Thoughts? . . .[/blue]

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [blue]Its Worthy Reading![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[3587ch]

But this will return two rows for each client, right?

I needed it to show all 5 fields in one row.

Like:

ClientID, LastPmtAmt, LastPmtDate, PrevPmtAmt, PrevPmtDate

Is this possible?

Thank you again,

 

[PHV]

Is this possible?
Sure, here an example using Ace's suggestion:

SELECT C.ClientID, L.PaymentDate AS LastDate, L.PaymentAmount AS LastAmount, P.PaymentDate AS PreviousDate, P.PaymentAmount AS PreviousAmount
FROM (tblClients C
INNER JOIN tblPayments L ON C.ClientID = L.ClientID)
INNER JOIN tblPayments P ON C.ClientID = P.ClientID
WHERE L.PaymentDate=(SELECT Max(PaymentDate) FROM tblPayments WHERE ClientID=C.ClientID)
AND P.PaymentDate In (SELECT TOP 2 PaymentDate FROM tblPayments WHERE ClientID=C.ClientID Order By PaymentDate DESC)
AND L.PaymentDate>P.PaymentDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[3587ch]

I tried the solution, however I get a syntax error: Missing Operator on this:

C.clientid = L.clientid
INNER JOIN tblpayments P ON C.clientid = P.clientid

Here is what I typed:

SELECT C.clientid, L.[paymentdate] as LastPmtdate, L.[Amount Paid] as LastPmtAmt, P.[paymentdate] as PrevPmtdate, P.[Amount Paid] as PrevPmtAmt
FROM tblclients C
INNER JOIN tblpayments L ON C.clientid = L.clientid
INNER JOIN tblpayments P ON C.clientid = P.clientid
WHERE L.[paymentdate]=(select Max([paymentdate]) FROM tblpayments
WHERE clientid = C.clientid)
AND P.[paymentdate] In (select top 2 [paymentdate] FROM tblpayments
WHERE clientid=C.clientid Order By [paymentdate] DESC) AND L.[paymentdate]>P.[paymentdate]);

 

[PHV]

FROM [!]([/!]tblclients C
INNER JOIN tblpayments L ON C.clientid = L.clientid[!])[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[3587ch]

That fixed the issue. It is very.... slow. My tblpayments table has about 25,000 rows. Do I need to have the tblclients table joined if the tblpayments table has the clientid in it? And would that make any chamge in the speed?

Thank you so much for your help on learning how this works.

 

[TheAceMan1]

3587ch . . .

Never type code. [blue]Always copy and paste![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

3587ch . . .

In tblPayments set an index (Yes (Duplicates OK)) to PaymentDate. Then test . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[3587ch]

So far everything has worked, but very slow.

Is it possible to just get the top 2 payments, but for all clients in the results?

One of the suggestions gets the top 2, but not for all clients. Can this ber done without using a subquery?

Thank you very much,

 

[PHV]

So, what about this ?

SELECT L.clientid, L.paymentdate AS LastPmtdate, L.[Amount Paid] AS LastPmtAmt
, P.paymentdate AS PrevPmtdate, P.[Amount Paid] AS PrevPmtAmt
FROM (SELECT B.clientid, B.paymentdate, B.[Amount Paid]
 FROM tblpayments AS B INNER JOIN tblpayments AS C
 ON  B.clientid=C.clientid AND B.paymentdate<=C.paymentdate
 GROUP BY B.clientid, B.paymentdate, B.[Amount Paid] HAVING Count(*)=1
) AS L
LEFT JOIN (SELECT B.clientid, B.paymentdate, B.[Amount Paid]
 FROM tblpayments AS B INNER JOIN tblpayments AS C
 ON  B.clientid=C.clientid AND B.paymentdate<=C.paymentdate
 GROUP BY B.clientid, B.paymentdate, B.[Amount Paid] HAVING Count(*)=2
) AS P ON L.clientid = P.clientid

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[3587ch]

PHV,

Well, this new way is so much faster, I can see the difference in the sql, but what is the explanation for why this works so much faster?

Thank you,

 

[PHV]

Just because it don't use any CORRELATED subquery ...

 

[TheAceMan1]

PHV . . .

I get a parameter box prompting for [blue]B.Amount Paid[/blue].

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

Works for me: ac2003sp3