[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Query To pull most recent payment and amount

Posted on 2015-01-21
9
Medium Priority
?
110 Views
Last Modified: 2015-01-21
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
Comment
Question by:mlcktmguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40562935
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
 
LVL 1

Author Comment

by:mlcktmguy
ID: 40562985
Thank you.  I don't see a qualifier to only include posted payments.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40563025
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 1

Author Comment

by:mlcktmguy
ID: 40563034
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40563061
ok. give a detail info about your table. not like this piece by piece of information
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40563064
post a sample expected result coming from your table.
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 40563085
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40563093
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
 
LVL 1

Author Closing Comment

by:mlcktmguy
ID: 40563159
Thank you, works correctly.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question