BEST SQL to join Customer and LAST Transaction

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)

BEST SQL to join Customer and LAST Transaction

Author

Topic

geossl
Yak Posting Veteran

85 Posts

Posted - 2003-10-31 : 02:17:42

Dear All,
There is a table, Customer,
CustomerID: (Primary Key)
CustomerName:

and a Transaction table which holds all the transactions of customer:
TransID: (Primary Key)
CustomerID:
TransDate:
Branch_ID:

What is the BEST SQL or Best way to get all the customer's
LAST transaction with customer name, LAST TransDate,
Branch_ID

The result should include the Branch_ID, that is:

CustomerName LastTDate LAST_Branch
---------- --------- -----------
Peter 2003/1/1 ATM_08
Mary 2003/7/1 ATM_38
Paul 2003/8/1 ATM_08

Moreover, this may lead to a problem of getting all the customer, TransDATE which LAST Transaction is performed at Branch_ID = "ATM_18".

Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-31 : 03:10:17

First thing that comes to mind is

select *
from Customer c
join Transaction t
on t.CustomerID = c.CustomerID
and t.TransID = (select max(TransID) from Transaction t2 where t2.CustomerID = t.CustomerID)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

homam
Starting Member

31 Posts

Posted - 2003-10-31 : 03:13:58

[code]
-- First query:

select c.CustomerName, t.TransDate, t.BranchID
from Customer c
join Transaction t on c.CustomerID = t.CustomerID
where t.TransID = (
select top 1 TransID
from Transaction
where CustomerID = c.CustomerID
order by TransDate desc
)

-- Second query (for a certain branch)
-- Same as above, just restrict the correlated subquery:

select c.CustomerName, t.TransDate, t.BranchID
from Customer c
join Transaction t on c.CustomerID = t.CustomerID
where t.TransID = (
select top 1 TransID
from Transaction
where CustomerID = c.CustomerID
and BranchID = 'ATM_18'
order by TransDate desc
)
[/code]

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-31 : 03:43:32

The 4th query:

select CustomerName,

(select max(TransDate) from Transaction t
where t.CustomerID=c.CustomerID) as LastTDate,

(select top 1 Branch_ID from Transaction t
where t.CustomerID=c.CustomerID order by TransDate desc) as LastBranch

from Customer as c

Don't mind Branch_ID = 'ATM_18'. Just put it into WHERE clauses of
the subqueries as one more filtering condition.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources