如何为每个客户返回单个事务 [英] How to return a single transaction per customer

查看:326
本文介绍了如何为每个客户返回单个事务的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我创建了一个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 

这篇关于如何为每个客户返回单个事务的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆