Click here to Skip to main content
15,033,028 members
Please Sign up or sign in to vote.
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
Updated 12-Jun-18 9:21am

1 solution

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.
   
Comments
Maciej Los 12-Jun-18 16:36pm
   
Pardon, i can only vote 5!
chints786 14-Jun-18 0:18am
   
@Richard:- Thanks a lot for the solution. However there is a small fight with this solution and I should have mentioned in the beginning that my SQL Server 2008 R2 is what I am using right now and the built in function (DateFromParts) is applicable from 2012.

Will be helpful if you could share replacement solution for this function.
Richard Deeming 14-Jun-18 8:19am
   
Try:
DateAdd(day, 1 - Day(TransactionDate), TransactionDate)

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900