如何为每个客户返回单个事务

问题描述：

我创建了一个Crystal Report，它使用SQL Server上的存储过程来返回在给定时间范围内进行交易的所有卡。

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.

答

（PK_Customer，FullName，CardNumber，NRTransactions，SchemeName，TransactionDate）

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

如何为每个客户返回单个事务

相关推荐