Extracting latest transactions

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)

Extracting latest transactions

Author

Topic

Leigh79
Starting Member

28 Posts

Posted - 2009-07-24 : 07:46:36

Hi Guys

I have a table of bookings. Each booking can have several transactions within this table.

What I would like to be able to do is for each Booking Number extract the last transaction that took place and the information on that row e.g From this table:

ID | Booking ID | Transaction Date | Leadname | Adults
-------------------------------------------------------
1 | 123456 | 01/07/2009 | TBA | 0
2 | 123457 | 01/07/2009 | TBA | 0
3 | 123458 | 01/07/2009 | BROWN | 2
4 | 123456 | 02/07/2009 | SMITH | 0
5 | 123456 | 03/07/2009 | SMITH | 2
6 | 123456 | 03/07/2009 | SMITH | 3
7 | 123457 | 03/07/2009 | JONES | 2

I would like to return:

Booking ID | Transaction Date | Leadname | Adults
--------------------------------------------------
123456 | 03/07/2009 | SMITH | 3
123457 | 03/07/2009 | JONES | 2
123458 | 01/07/2009 | BROWN | 2

How would I go about doing this?
Thanks
Leigh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-24 : 07:51:58

[code]
select [Booking ID] , [Transaction Date] , [Leadname] | [Adults]
from bookings b
inner join
(
select [Booking ID],
[Transaction Date] = max([Transaction Date]),
[ID] = max([ID])
from bookings
group by [Booking ID]
) m on b.[Booking ID] = m.[Booking ID]
and b.[Transaction Date] = m.[Transaction Date]
and b.[ID] = m.[ID]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Leigh79
Starting Member

28 Posts

Posted - 2009-07-24 : 07:59:30

Hi khtan

Thanks for replying so quickly - I'll try this out and let you know how I get on!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources