Extract last 5 transactions for all customers

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)

Extract last 5 transactions for all customers

Author

Topic

denis
Starting Member

3 Posts

Posted - 2004-07-14 : 09:35:49

I have a Table in a database that stores histories of activities with customers. It is a one to many relationship with the customer table.

Is it possible to create a select query that would give me only the last 5 transactions for each customer.

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-14 : 10:03:36

what is the primary key of your transactions table? how do we determine the "last" transactions? (i.e., is there a transaction date column?)

- Jeff

denis
Starting Member

3 Posts

Posted - 2004-07-14 : 10:13:14

In the History Table there is a primary key on the field called RecID (varchar), there is an AccountNo (varchar) field which allows join to Customer Table and there is a CreatOn date field (datetime).

The CREATEON field would be the transaction date.

Hope this helps, thanks.

denis
Starting Member

3 Posts

Posted - 2004-07-20 : 06:11:40

Can anyone advise on how to create this query - last N transactions for each customer in their history file.

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-20 : 09:49:21

this might not be super efficient, but here is one way:

1. calculate the "rank" of each transaction per customer:


select AccountID, recID, CreatOn, 
   (select count(*) from Hist B where A.AccountID = B.AccountID and B.CreatOn >= A.CreatOn) as Rank
from Hist A

Look at how that is working, check it out. then you can query this table, selecting the top 5 rankings per customer with a WHERE clause:


select * from (above SQL) A where Rank <= 5

This will not be very efficient, however, if you have lots of transactions.

- Jeff

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-20 : 09:57:03

Assuming...
Customer is your customer table
History is your history data

Using TOP 5 will return the top 5 records, and ordering by the date in descending sequence puts the latest records at the top.
TOP 5 then will give you the 5 most recent records.


Select TOP 5 C.CustomerData, H.HistoryData
from History H
INNER JOIN Customer C ON C.AccountNo=H.AccountNo
ORDER BY H.CreatOn DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources