How to return a single transaction per customer

Question

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 card has been used, and then return the last row (transaction) that the card has made. This should mean that I only return one row per card, regardless of how many transactions are made.

However, when I create a report my output looks like this:

My stored procedure looks like this:

ALTER PROCEDURE [dbo].[RptCardUseDateRange]
    -- Add the parameters for the stored procedure here
    @DateStart datetime,
    @DateEnd datetime

AS

BEGIN

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

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

WHERE
    TransactionDate BETWEEN @DateStart AND @DateEnd

ORDER BY PK_Customer desc, CardNumber 

END

So SO my question is, what should I add to only return one row per card number?

From other posts I have looked at, I have an idea it is something like:

cross apply(select top 1 PK_Transaction From [Transaction] T where T.FK_Card = C.PK_Card) X

But this does nothing, as I suspect I am putting the wrong parameters in.

Answer 1

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:

Group By PK_Customer, CardNumber, NRTransactions, SchemeName, DateOfLastTransaction

Answer 2

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 
        INNER JOIN CardStatus CS ON C.FK_CardStatus = CS.PK_CardStatus
        LEFT JOIN Customer CU ON C.FK_Customer = CU.PK_Customer
        INNER JOIN [User] U ON CU.FK_User = U.PK_User
        INNER JOIN [Scheme] S ON CU.FK_Scheme = S.PK_Scheme
        INNER JOIN [Transaction] T ON C.PK_Card = T.FK_Card

    WHERE
        TransactionDate BETWEEN @DateStart AND @DateEnd
)

SELECT d.* 
FROM DATA d 
INNER JOIN (
            SELECT CardNumber, MAX(TransactionDate) AS TransactionDate 
            FROM DATA 
            GROUP BY CardNumber
            ) md ON d.CardNumber=md.CardNumber and d.TransactionDate = md.TransactionDate
      ORDER BY d.PK_Customer desc, d.CardNumber