How to see if a record appears two times

[_nappi]

Hello everyone! I need help figuring out a simple problem (I think): I have a table where each row shows the quote document created by an agent for a customer and its timestamp. Of course, every quote, agent, and customer document has a unique ID.

When the same agent creates multiple quote documents on the same customer in the same month, those quote documents must be rejected, otherwise they must be taken into account.

So, how can I create a flag that tells me if there is more than one estimate document created by the same agent for the same customer in the same month?

Thank you in advance

[Aditya_Chitale]

Like this ?

test:
load * Inline
[
agent,customer,month,region
abc,aditya,jan,AUS
abc,aditya,jan,USA
xyz,soham,feb,JPN
xyz,aditya,feb,IND
lmn,rohit,mar,BRZ
];

left join (test)

test_2:
load
agent,
customer,
month,
count(1) as distinct_count
resident test group by agent, customer, month;

drop table test;

Regards,

Aditya

[ASEDavidSu]

[_nappi]

Hi all,

thanks a lot but these solutions don't work ( I mean I didn't able to do).

I try to explain better: 

i need to count if, in the same month, the same agent on the same customer has done two quote documents. If yes, i need to rejected. 

Example:

Quote Document	Agent	ID Costumer	Timestamp
85876	F269	1523072	04/01/2023 10:47:15
85876	F269	1523072	04/01/2023 10:47:15
85876	F269	1523072	04/01/2023 10:47:15
85876	F269	1523072	04/01/2023 10:47:15
85877	F269	1523072	04/01/2023 10:47:45
85877	F269	1523072	04/01/2023 10:47:45
85877	F269	1523072	04/01/2023 10:47:45
85877	F269	1523072	04/01/2023 10:47:45

In this case, the quote document 85876 (in red) must be rejected because i had yet a quote document on the same customer done by the same agent. I need something that counts the max record on the same customer on the same agent.

Thank you again in advance

[_nappi]

Hi,

thanks a lot but these solutions don't work ( I mean I didn't able to do).

I try to explain better: 

i need to count if, in the same month, the same agent on the same customer has done two quote documents. If yes, i need to rejected. 

Example:

Quote Document	Agent	ID Costumer	Timestamp
85876	F269	1523072	04/01/2023 10:47:15
85876	F269	1523072	04/01/2023 10:47:15
85876	F269	1523072	04/01/2023 10:47:15
85876	F269	1523072	04/01/2023 10:47:15
85877	F269	1523072	04/01/2023 10:47:45
85877	F269	1523072	04/01/2023 10:47:45
85877	F269	1523072	04/01/2023 10:47:45
85877	F269	1523072	04/01/2023 10:47:45

In this case, the quote document 85876 (in red) must be rejected because i had yet a quote document on the same customer done by the same agent. I need something that counts the max record on the same customer on the same agent.

Thank you again in advance