• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 111
  • Last Modified:

Query To pull most recent payment and amount

I know I can do this in code but I'm guessing there is a way to do it with a query, or 2.

These are the pertinent fields in my payment table:
ID                              :AutoNumber
TaxRecID                  :Id of the tax record payment is for.  There may be multiple, or no payments for a given tax record.
PaymentDate
PaymentAmount
PostedStatus           :2 = posted.  Posted payments are the only one's to be considered.

For any taxrecid's that have a posted payment, list the most recent posted payment, paymentdate and paymentamount.   At most one payment should be shown for each taxrecid.

If there are no payments for a taxrecID te taxrecID does not have to be in the query results.
0
mlcktmguy
Asked:
mlcktmguy
  • 5
  • 4
1 Solution
 
Rey Obrero (Capricorn1)Commented:
select a.TaxRecID, a.PaymentDate, a.PaymentAmount
from tablex as a inner join
(select  b.TaxRecID, max(b.PaymentDate) as maxPayDate
  from tablex as b
  group by b.taxRecId) as c
on a.TaxRecID= c.TaxRecID and a.PaymentDate = c.maxPayDate
0
 
mlcktmguyAuthor Commented:
Thank you.  I don't see a qualifier to only include posted payments.
0
 
Rey Obrero (Capricorn1)Commented:
what do you mean? are you referring to PostedStatus?



select a.TaxRecID, a.PaymentDate, a.PaymentAmount, a.PostedStatus
from tablex as a inner join
(select  b.TaxRecID, max(b.PaymentDate) as maxPayDate
  from tablex as b
  group by b.taxRecId) as c
on a.TaxRecID= c.TaxRecID and a.PaymentDate = c.maxPayDate
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mlcktmguyAuthor Commented:
Yes, only posted payment should be considered in the selection.  There are also 'NSF' and 'Unposted' payments in the table.  We don't want those included.
0
 
Rey Obrero (Capricorn1)Commented:
ok. give a detail info about your table. not like this piece by piece of information
0
 
Rey Obrero (Capricorn1)Commented:
post a sample expected result coming from your table.
0
 
mlcktmguyAuthor Commented:
The detail example of my table is in the original post:

"These are the pertinent fields in my payment table:
 ID                              :AutoNumber
 TaxRecID                  :Id of the tax record payment is for.  There may be multiple, or no payments for a given tax record.
 PaymentDate
 PaymentAmount
 PostedStatus           :2 = posted.  Posted payments are the only one's to be considered.

 For any taxrecid's that have a posted payment, list the most recent posted payment, paymentdate and paymentamount.   At most one payment should be shown for each taxrecid.

 If there are no payments for a taxrecID te taxrecID does not have to be in the query results. "
0
 
Rey Obrero (Capricorn1)Commented:
post the result of this query

select a.TaxRecID, a.PaymentDate, a.PaymentAmount, a.PostedStatus
from tablex as a inner join
(select  b.TaxRecID, max(b.PaymentDate) as maxPayDate
  from tablex as b
  group by b.taxRecId) as c
on a.TaxRecID= c.TaxRecID and a.PaymentDate = c.maxPayDate
where a.PostedStatus=2
0
 
mlcktmguyAuthor Commented:
Thank you, works correctly.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now