Make a "distinct" in a query with many fields in select

Question

In this query, can I do a sort of distinct? A Group By is very slow and there are many fields. Pure distinct does not solve, as there are several fields. How would that be?

DECLARE
@CodTerceiro int = 113773,  
@DataVencimentoIni datetime = null,   
@DataVencimentoFim datetime = null,   
@Pago  tinyint = 3    

SELECT pw.IdPedidoWeb,
        pw.IdPedido,
        Convert(DateTime,receber.E1_EMISSAO,103) as DataEmissao ,  
        Convert(DateTime,receber.E1_VENCTO, 103) as DataVencimento,     
        pedido.C5_XIDNF AS IdNotaFiscal,    
        pedido.C5_NOTA AS NroNotaFiscal, 
        receber.E1_VALOR AS VlrTotalNotaFiscal,

        CASE WHEN receber.E1_SALDO > 0
        THEN 'Em Aberto'
        ELSE 'Pago'
        END 'Status',

        pw.CodProduto,    
        pw.QtdItem,    
        pw.QtdItem * pw.VlrVenda 'VlrProduto'    
    FROM [dbo].[PedidoWeb] pw    
        WITH (NOLOCK, INDEX (Ind_PedidoWeb_2)) 

        INNER JOIN Totvs12.dbo.SC5010 pedido
        --WITH (NOLOCK)
        ON pedido.C5_XPEDSND = pw.IdPedido

        INNER JOIN Totvs12.dbo.SE1010 receber 
        --WITH (NOLOCK)
        ON receber.E1_FILIAL + receber.E1_NUM + receber.E1_PREFIXO = pedido.C5_FILIAL + pedido.C5_NOTA + pedido.C5_SERIE    

        WHERE pw.CodTerceiro = @CodTerceiro   
        AND Convert(DateTime,receber.E1_VENCTO, 103) >= coalesce (@DataVencimentoIni, Convert(DateTime,receber.E1_VENCTO, 103))    
        AND Convert(DateTime,receber.E1_VENCTO, 103) <= coalesce (@DataVencimentoFim, Convert(DateTime,receber.E1_VENCTO, 103))    

        AND 
        (   @Pago = 3    
            OR @Pago = CASE WHEN receber.E1_SALDO > 0 THEN 1 ELSE 2 END)

        AND pedido.D_E_L_E_T_ = ''

        AND pw.IdPedidoWEb between '9000000' and '20000000' 

In the query below I return only 75 records and in the query above 213

select distinct idpedido from PedidoWeb where codterceiro = 113773 AND IdPedidoWEb between '9000000' and '20000000' 
and IdPedido  in (select distinct c5_xpedsnd from Totvs12.dbo.SC5010 where C5_Cliente = '113773')

The above query should have the same result, except something I don’t know.

Answer 1

(1)
Before entering the issue of 75 x 213 returned lines, I suggest you review the clauses FROM/ON and WHERE from the point of view of sargability. For example, in the FROM clause of your code there is the following construction:

INNER JOIN Totvs12.dbo.SE1010 receber 
--WITH (NOLOCK)
ON receber.E1_FILIAL + receber.E1_NUM + receber.E1_PREFIXO = pedido.C5_FILIAL + pedido.C5_NOTA + pedido.C5_SERIE

This concatenation of columns is nothing more than an expression, which makes the predicate non sargable. If the columns receive.E1_FILIAL, receive.E1_NUM, and receive.E1_PREFIXO correspond to the columns requested.C5_FILIAL, request.C5_NOTA and request.C5_SERIE, here is a suggested change:

INNER JOIN Totvs12.dbo.SE1010 receber 
ON receber.E1_FILIAL = pedido.C5_FILIAL
   and receber.E1_NUM = pedido.C5_NOTA
   and receber.E1_PREFIXO =  pedido.C5_SERIE

---

(2)
In the WHERE clause there are also predicates that need revision. The code snippet

   ...
   AND Convert(DateTime,receber.E1_VENCTO, 103) >= coalesce (@DataVencimentoIni, Convert(DateTime,receber.E1_VENCTO, 103))    
   AND Convert(DateTime,receber.E1_VENCTO, 103) <= coalesce (@DataVencimentoFim, Convert(DateTime,receber.E1_VENCTO, 103))    
   ...

needs to be rewritten because Convert(DateTime,receber.E1_VENCTO, 103) makes the predicate non sargable.

From what I have observed on other topics, the E1_VENCTO column is declared as char(8) and in the format yyyymmdd. If this is the case, I suggest converting types in the parameter, converting it from datetime to char(8). This guarantees sargability to the predicate.

   ...
   AND receber.E1_VENCTO >= coalesce (convert( char(8), @DataVencimentoIni, 112), receber.E1_VENCTO)    
   AND receber.E1_VENCTO <= coalesce (convert( char(8), @DataVencimentoFim, 112), receber.E1_VENCTO)    
   ...

Also evaluate the following alternative:

   ...
   AND (@DataVencimentoIni is null OR receber.E1_VENCTO >= convert( char(8), @DataVencimentoIni, 112))   
   AND (@DataVencimentoFim is null OR receber.E1_VENCTO <= convert( char(8), @DataVencimentoFim, 112))    
   ...

On the issue of sargability, I suggest reading the article "Building Efficient T-SQL Code: Sargability”.

---

(3)
Regarding the line return difference, 75 x 213, it is probably due to the wrong date conversion in this code snippet:

Convert(DateTime,receber.E1_VENCTO, 103)

Considering that the E1_VENCTO column declared as char(8) and in the format yyyymmdd, the correct is

Convert(DateTime,receber.E1_VENCTO, 112)

But it is also necessary to review the question of cardinality, cited by Mariana.