Selecting Last Transaction From History

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting Last Transaction From History

Author

Topic

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.

MevaD
Starting Member

32 Posts

Posted - 2010-05-06 : 17:00:19

Sorry - I posted this in the wrong area. I just reposted in the "New to SQL Programming" section instead...

This post can be deleted.

Again, sorry.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources