Help Me with a query
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT FIRST 1 * FROM PAYMENT WHERE InvoiceID=1234567 ORDER BY PaymentDate
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)
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)ASKER
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"
@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"
ASKER
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?
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?
ASKER
is this possible?
select a.invoiceid, b.paymentid
from invoice a
inner join (select first 1 paymentid
from payment where invoice.invoiceid=payment.
order by paymentdate desc, paymentid desc) b
on 1=1
select a.invoiceid, b.paymentid
from invoice a
inner join (select first 1 paymentid
from payment where invoice.invoiceid=payment.
order by paymentdate desc, paymentid desc) b
on 1=1
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
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
>>@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
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)ASKER
@aflockhart: The query didn't work. "where invoiceid=a.invoiceid", table a is unknown in derived table. Any other idea?
ASKER
@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.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
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)