CODEWITHSUNDEEP
sqlsql-servermaxaggregate
Ryan Miller
Ryan Miller

Reputation: 315

SQL to find the most recent account transaction for each customer

EDIT: I'm using SQL Server

I looked around for an example of this but couldn't find anything so I'll start a new thread...

I have 3 tables.

Account

AccountEnroll

AccountType

The AccountEnroll table is a bridge table to track each customer's enrollment history. I want to use the "EnrollDate" column to determine the current account type for each customer. I need to write a SELECT statement that can display AccountID, FirstName, LastName, (current)AccountType.

I am having trouble getting my resultset to display only the MAX(EnrollDate) record for each customer.

Upvotes: 2

Views: 3183

Answers (3)

acatt
acatt

Reputation: 487

You could use a correlated sub-query:

SELECT A.AccountId, A.FirstName, A.LastName, AT.AccountType
FROM Account A
JOIN AccountEnroll AE
    ON A.AccountId = AE.AccountId
JOIN AccountType AT
    ON AE.AccountTypeId = AT.AccountTypeId
WHERE NOT EXISTS (
    SELECT 1
    FROM AccountEnroll
    WHERE AccountId = AE.AccountId
        AND EnrollDate > AE.EnrollDate
)

Upvotes: 0

Ben Thul
Ben Thul

Reputation: 32667

You can use common table expressions to do this pretty simply.

with cte as (

select A.FirstName, A.LastName, AT.AccountType, AE.EnrollDate, row_number() over (partition by AE.AccountID order by EnrollDate desc) as [rn]
from Account as A
inner join AccountEnrolled as AE
   on A.AccountId = AE.AccountId
inner join AccountType as AT
   on AE.AccountTypeId = AT.AccountTypeId

)
select FirstName, LastName, AccountType, EnrollDate
from cte
where rn = 1

Upvotes: 4

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

Try this:

SELECT 
  a.AccountID, a.FirstName, a.LastName, 
  at.AccountType AS 'Current AccountType'
FROM Account a
INNER JOIN
(
   SELECT AccountID, MAX(EnrollDate) MaxDate
   FROM AccountEnroll
   GROUP BY AccountID
) t
INNER JOIN AccountEnroll ae ON  ae.AccountID = t.AccountID 
                            AND ae.EnrollDate = t.MaxDate
INNER JOIN AccountType at ON ae.AccountTypeID = at.AccountTypeID

Upvotes: 2

Related Questions