sql server - SQL query that returns payment amounts that occur more than once per customer but excludes amounts with a single occurrence

Question

If I have a [Customer] who makes a [Payment] on a given [Date] Then how can I query multiple occurrences of the same payment value on customer-by-customer basis, without specifying the Customer?

I would want to return something like this:

[Customer]	[Payment]	[Date]
A	5	1/1
A	5	1/4
B	4	1/2
B	4	1/3
C	9	1/1
C	9	1/5

question from:https://stackoverflow.com/questions/66051551/sql-query-that-returns-payment-amounts-that-occur-more-than-once-per-customer-bu

Answer 1

In MSSQL Server, partition by is very useful.You can group the columns and give new values.

MSSQL Server partition by

In this situation, Customer name and Payment date must be grouping so duplicate values are findable.

Customer table and payment table join code :

select c.Name,p.PaymentDate,p.PaymentValue from dbo.Customer c inner join dbo.Payment p on c.CustomerId = p.CustomerId order by 1,2 

Customer table and Payment table example values:

Query for finding duplicate values:

;WITH CTE AS ( SELECT c.Name,p.PaymentDate,p.PaymentValue,ROW_NUMBER() OVER (PARTITION BY c.Name,p.PaymentValue ORDER BY p.PaymentDate asc) AS [rn] from dbo.Customer c inner join dbo.Payment p on c.CustomerId = p.CustomerId ) select * from cte where rn>1

Query Result: