Sql server query to find no of transactions per day and also in a month

Question

0.00/5 (No votes)

See more:

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

Posted 12-Jun-18 8:38am

chints786

Updated 12-Jun-18 9:21am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2018-06-12T09:23:00

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

So something like this?

SQL

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
;

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.