|
MevaD
Starting Member
32 Posts |
 Posted - 2010-05-06 : 16:54:20
|
Hello,
I have a situation where I need to find the last line item from the last transaction for each invoice.
Here's the setup (NOTE: I did not design the table or database):
Invoice table is linked to Transaction table by CustID and TransID.
-------------
Invoice Table
-------------
CustID
InvID
InvoiceStatus
-----------
Trans Table
-----------
CustID
InvID
TransactionDate
LineItemID (smallint)
SAMPLE DATA
Invoice
1, 3, Open
1, 4, Closed
2, 3, Open
3, 1, Open
Trans
1, 3, 04-30-2010, 1
1, 3, 04-30-2010, 2
1, 3, 04-30-2010, 3
2, 3, 05-01-2010, 1
2, 3, 05-01-2010, 2
3, 1, 05-01-2010, 1
So the query should return:
1, 3, Open, 1, 3, 04-30-2010, 3
2, 3, Open, 2, 3, 05-01-2010, 2
3, 1, Open, 3, 1, 05-01-2010, 1
I need to be able to link the record with the largest LineItemID AND latest TransactionDate for all 'Open' invoices in the Invoice table.
The DBMS is MS-SQL 2005.
Thanks for any ideas. |
|
Selecting Last Transaction From History