15,033,028 members
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.

What I have tried:

I tried using group by ...

But unable to get the desired result
Posted
Updated 12-Jun-18 9:21am

Solution 1

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.
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)`