Sql server查询每天以及一个月内找不到任何事务 [英] Sql server query to find no of transactions per day and also in a month

问题描述

有一张表，其中包含11个lac plus记录。该表有3列



CustomerId TransactionDate TransactionType



在上表中有大约9个不同的lac在一个月内每天进行交易的客户。



对于单个客户完成的每笔交易，他获得5分。



在一天中，客户有资格进行4笔交易，因此他有资格获得一天20分。



请注意客户是免费的在一天内做'N'没有交易，但是要求说冻结他一天4次交易和一个月10次交易。



这已经实现由我。



我面临的问题是我想知道9个不同的客户中有没有办法找出客户有多少交易在一个月内和一天内完成



有点像这样： -



CustomerID transactindate类型

1 2016-01-01 a

1 2016-01-01 a

1 2016-01-01 a

1 2016-01-02 a



输出： -

CustomerID day_count月份积分

1 3 4 20





请注意同一客户可以在同一年进行多次交易，但在多个月的不同月份。



请帮助



我的尝试：



我尝试使用group by ...



但无法获得所需的结果

Hi,

There is a table which consists of 11 lac plus records in it. The table has 3 columns

CustomerId TransactionDate TransactionType

In the above table there are approx 9 lac distinct customers who have done transactions on daily basis in a Month.

For every Transaction done by a single customer he gets 5 points.

In a day customer is eligible to do 4 transactions thereby he will be eligible for 20 points in a day.

Please note customer is free to do 'N' no of transactions in a day but the requirement says freeze him for 4 transactions in a day and 10 transactions in a month.

This has been achieved by me.

The problem which I am facing is I want to know that out of 9 lac distinct customers is there a way to find out how many transactions a customer has done in a month and also in a day

Somewhat like this:-

CustomerID transactindate type
1 2016-01-01 a
1 2016-01-01 a
1 2016-01-01 a
1 2016-01-02 a

output:-
CustomerID day_count Month points eligible
1 3 4 20


Please note the same customer can do in the same year many transactions but in different month on multiple days.

Please help

What I have tried:

I tried using group by ...

But unable to get the desired result

推荐答案

引用：

要求说他在一天内冻结了4笔交易，并且10个transa一个月内的ctions

the requirement says freeze him for 4 transactions in a day and 10 transactions in a month

这样的事情是什么？

So something like this?

WITH cteByDay As
(
    SELECT
        CustomerId,
        TransactionDate,
        Count(1) As TransactionCount
    FROM
        YourTable
    GROUP BY
        CustomerId,
        TransactionDate
),
cteByMonth As
(
    SELECT
        CustomerId,
        DateFromParts(Year(TransactionDate), Month(TransactionDate), 1) As TransactionMonth,
        SUM(CASE 
            WHEN TransactionCount > 4 THEN 4 
            ELSE TransactionCount 
        END) As TransactionCount
    FROM
        cteByDay
    GROUP BY
        CustomerId,
        DateFromParts(Year(TransactionDate), Month(TransactionDate), 1)
)
SELECT
    CustomerId,
    TransactionMonth,
    CASE
        WHEN TransactionCount > 10 THEN 10 
        ELSE TransactionCount 
    END As TransactionCount
FROM
    cteByMonth
ORDER BY
    CustomerId,
    TransactionMonth
;

这将为您提供月份和客户ID的列表，该客户在该月内的交易数量，限制为每天最多4笔交易，或每月10笔交易。

This will give you a list of months and customer IDs, with the number of transactions for that customer within that month, limited to a maximum of 4 transactions per day, or 10 transactions per month.

这篇关于Sql server查询每天以及一个月内找不到任何事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！