Query duplicate numeric field in sql server

Question

Good morning!

I have a table of notes and in the month of December there was a duplicity of several notes. These duplicated notes have different numbers, however, the total value of the note is exactly equal.

I tried using having Count and did not succeed in the research.

How can I identify these notes with different numbers and equal values? The field of the total value that is the duplicate is numerical.

Below is an example of one of the duplicate notes.

select numpdv, on a note, total value, dated, id_nfe from tab_nota_header Where dataemitted between '2020-12-04 00:00:00' and '2020-12-04 23:59:59' and codloja=3 and numpdv=21 and total value=14.00

The result of the search

By doing this select I can identify which values are repeating.

total value, Count(total value) from tab_nota_header Where dataemitted between '2020-12-04 00:00:00' and '2020-12-04 23:59:59' and codloja=3 and numpdv=21 group by total value having Count(total value) > 1

He returns me this result

The problem is that I need the note number and the pdv number and when I add these fields in the query, it does not bring the result I need.

Answer 1

Good afternoon!

Imex, you didn’t help me, in fact you saved me.

It worked the query and now I will be able to follow the correction I need.

We can close. Again, thank you very much!

Answer 2

Good afternoon,

Here is a suggestion for testing using the function Count with the trunks Over and Partition By to obtain the number of rows with the same value in the columns total value, numpdv and date issuance:

with CTE_Count as
(
  select 
    numpdv, 
    numnota, 
    valortotal, 
    dataemissao, 
    id_nfe,
    count(*) over(partition by valortotal, numpdv, dataemissao) as QtdMesmoValor
  from tab_nota_header 
  where 
    dataemissao between '2020-12-04 00:00:00' and '2020-12-04 23:59:59' and 
    codloja = 3 and 
    numpdv = 21
)

select * from CTE_Count
where QtdMesmoValor > 1

I hope it helps