Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help Me with a query

Posted on 2011-05-11
14
Medium Priority
?
286 Views
Last Modified: 2012-05-11
Hi experts,

I have 2 tables

INVOICE TABLE
-----------------------------
InvoiceID
CustomerID
InvoiceDate
InvoiceAmt


PAYMENT TABLE
----------------------
PaymentID
PaymentDate
InvoiceID
AmountPaid



1 invoice may have more than one payment on the same date. I need to have a query that will list last payment of certain invoice. Can you help me with the query? Should i use stored proc to achieve this, or can you use just a simple query. I use firebird rdbms.

Thanks in advance.
0
Comment
Question by:reynaldio
  • 6
  • 3
  • 3
  • +2
14 Comments
 
LVL 17

Expert Comment

by:aflockhart
ID: 35739040
Are the payment ID's generated in order ? I assume they are, because if not, you can't tell which of two invoices on the same date is the later one.

Assuming the IDs are in increasing order it will be something like this  assuming you are looking for the latest payment on invoice 999 - and I don't know firebird, but this is standard SQL:

select * from payment
where paymentID=(select max paymentID from payment where invoiceid=999)







0
 
LVL 2

Accepted Solution

by:
neo00000110 earned 500 total points
ID: 35739046

Hi,

I don't know Firebird rdbms but the sql i would use is something like below, maybe it will help!


select top 1 amountpaid
from [Payment Table]
where invoiceid = 1
order by paymentdate desc, paymentid desc
0
 
LVL 3

Expert Comment

by:yadaya
ID: 35739050
SELECT FIRST 1 * FROM PAYMENT WHERE InvoiceID=1234567 ORDER BY PaymentDate

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 41

Expert Comment

by:ralmada
ID: 35739059
try the attached.

or you can alternatively try if PaymentID is cronological and sequencial

....where B.PaymentId = (select max(paymentID) from Payment where InvoiceID = B.InvoiceID)

select	A.*,
	B.*	
from Invoice A
inner join Payment B on A.InvoiceID = b.InvoiceID
where B.PaymentDate = (select max(paymentDate) from Payment where InvoiceID = B.InvoiceID)

Open in new window

0
 
LVL 2

Author Comment

by:reynaldio
ID: 35739545
Thank you all for your superfast comments

@aflockhart: i cannot use only payment id or paymentdate. I need to use max(paymentdate) and max(paymentid), since it is possible that user make payment but set the payment date before the actual latest payment.

@ralmada & @yadaya: i also cannot use your query, since it is possible that invoice has 2 payments on the same date.

@neo00000…: i think i can try that query. i will just have to replace "top" to "first"
0
 
LVL 2

Author Comment

by:reynaldio
ID: 35739602
i've come up with this query.

select  a.invoiceid, b.paymentid
from invoice a
inner join (select first 1 paymentid, invoiceid from payment order by paymentdate desc, paymentid desc) b
on a.invoiceid=b.invoiceid


But i dont think this will work, since "select first 1 paymentid, invoiceid" might only return 1 record and might not be the record for the current invoice. Any idea?
0
 
LVL 2

Author Comment

by:reynaldio
ID: 35739631
is this possible?


select  a.invoiceid, b.paymentid
from invoice a
inner join (select first 1 paymentid
from payment where invoice.invoiceid=payment.invoiceid
order by paymentdate desc, paymentid desc) b
on 1=1
0
 
LVL 17

Expert Comment

by:aflockhart
ID: 35739723
How about this ( added the line in bold to your previous query)

select  a.invoiceid, b.paymentid
from invoice a
inner join (select first 1 paymentid, invoiceid from payment
where invoiceid=a.invoiceid
order by paymentdate desc, paymentid desc) b
on a.invoiceid=b.invoiceid

0
 
LVL 41

Expert Comment

by:ralmada
ID: 35739818
>>@ralmada & @yadaya: i also cannot use your query, since it is possible that invoice has 2 payments on the same date.<<

That's why I told you to replace PaymentDate with PaymentID

select	A.*,
	B.*	
from Invoice A
inner join Payment B on A.InvoiceID = b.InvoiceID
where B.PaymentID = (select max(paymentID) from Payment where InvoiceID = B.InvoiceID)

Open in new window

0
 
LVL 2

Author Comment

by:reynaldio
ID: 35751525
@aflockhart: The query didn't work. "where invoiceid=a.invoiceid", table a is unknown in derived table. Any other idea?
0
 
LVL 2

Author Comment

by:reynaldio
ID: 35751533
@ralmada: i also cannot use just paymentid. since it is possible that user make payment but set the payment date before the actual latest payment.
0
 
LVL 17

Expert Comment

by:aflockhart
ID: 35752677
Looking back at previous comments - did you try the suggestion from neo0000 ?

If you only need the details for one invoice at a time, you might be able to build a stored procedure based on their suggestion, where you supply a parameter with the invoice nunber that you want.  I don;t know enough about your database software to know what it supports or how you would set it up.
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 500 total points
ID: 35754451
If it's just one invoice that you're looking for then yeah

select first 1 * from yourtable where invoicedID = .... order by PaymentDate desc

should be sufficient.

If what you're looking is the last payment for all invoices, then probably like this:
select	A.*,
	B.*	
from Invoice A
inner join Payment B on A.InvoiceID = b.InvoiceID
where B.PaymentID = (select first 1 paymentID from Payment where InvoiceID = B.InvoiceID order by PaymentDate desc, PaymentID desc)

Open in new window

0
 
LVL 2

Author Closing Comment

by:reynaldio
ID: 36387312
Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What we learned in Webroot's webinar on multi-vector protection.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

564 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