How To Return A Single Transaction Per Customer

I have created a Crystal Report that uses a stored procedure on my SQL Server to return all cards that make a transaction within a given time-scale. I intend to only identify if a

Solution 1:

You need to find the MAX transaction date for each card and get just those records

WITH Data(PK_Customer,FullName, CardNumber, NRTransactions, SchemeName, TransactionDate)
AS
(
    SELECT 
        PK_Customer,
        dbo.getCustomerFullName(PK_Customer) AS FullName,
        CardNumber,
        NRTransactions,
        SchemeName,
        DateOfLastTransaction,
        TransactionDate

    FROM 
        [Card] C 
        INNERJOIN CardStatus CS ON C.FK_CardStatus = CS.PK_CardStatus
        LEFTJOIN Customer CU ON C.FK_Customer = CU.PK_Customer
        INNERJOIN [User] U ON CU.FK_User = U.PK_User
        INNERJOIN [Scheme] S ON CU.FK_Scheme = S.PK_Scheme
        INNERJOIN [Transaction] T ON C.PK_Card = T.FK_Card

    WHERE
        TransactionDate BETWEEN@DateStartAND@DateEnd
)

SELECT d.*FROM DATA d 
INNERJOIN (
            SELECT CardNumber, MAX(TransactionDate) AS TransactionDate 
            FROM DATA 
            GROUPBY CardNumber
            ) md ON d.CardNumber=md.CardNumber and d.TransactionDate = md.TransactionDate
      ORDERBY d.PK_Customer desc, d.CardNumber 

Solution 2:

Found the Answer.

Looking at this post, it would seem that I was being silly and overcomplicating things.

All I had to change was TransactionDate in SELECT to min(TransactionDate) and then group the rest to create:

GroupBy PK_Customer, CardNumber, NRTransactions, SchemeName, DateOfLastTransaction