How to "reset" the other SQLSERVER line

Question

I have a query that returns to me how much particular customer bought from a certain product.

The problem is in the item(product) because it has more than one feature that causes duplicity in the value that he purchased overall.

In the query is made the junction with the same table because I need to know how much he bought in general compared to, for example, 2018 e 2019.

  OINV - tabela mãe onde pego o código do cliente.
  INV1 - tabela filha da OINV onde fica o valor das vendas.
  [DBO].[ItemCaracteristica] - View que retorna as características deste item.

DECLARE
        @DATA INT

SET @DATA = 2019
SELECT 
       TB.Cliente,
       TB.CODIGO,
       TB.Descricao,
       tb.Caracteristica,
       TB.ANOAnterior,
       TB.ValorAnterior,
       TB.ANOATUAL,
       [ValorAtual] = SUM(TB.ValorAtual)


FROM (
        SELECT DISTINCT
            [Cliente] = T0.CardCode,
            [CODIGO] = T4.item,
            [Descricao] = T4.Descricao,
            [Caracteristica] = t4.caracteristica,
            [ANOAnterior] = YEAR(T1.DocDate), 
            [ValorAnterior] = T3.LineTotal,
            [AnoAtual] = YEAR(T0.DocDate), 
            [ValorAtual] = T2.LineTotal
            --CASE WHEN YEAR(T1.DocDate) IS NULL THEN 100 ELSE SUM(T0.DocTotal) / SUM(T1.DocTotal) END,
            --[CALCULO] = IIF(YEAR(T1.DocDate) IS NULL, 100 , SUM(T2.LineTotal) / SUM(T3.LineTotal))
        FROM OINV T0
        LEFT JOIN OINV T1 ON T0.CardCode = T1.CardCode AND YEAR(T1.DocDate) = @DATA -1
        INNER JOIN INV1 T2 ON T0.DocEntry = T2.DocEntry AND T2.ItemCode = '1000'
        LEFT JOIN  INV1 T3 ON T1.DocEntry = T3.DocEntry AND T3.ItemCode = '1000'
        INNER JOIN [DBO].[ItemCaracteristica] T4 ON T2.ItemCode = T4.Item
        WHERE YEAR(T0.DocDate) = @DATA
        AND T0.CANCELED = 'N'
--GROUP BY T0.Cardcode,T2.LineTotal, T3.LineTotal,YEAR(T1.DocDate), YEAR(T0.DocDate) 
) AS TB 
GROUP BY tb.Cliente, TB.CODIGO, TB.Descricao, tb.Caracteristica,
         TB.ANOAnterior, TB.AnoAtual, ValorAnterior

Answer 1

For me there is no duplicity, because in the column "Feature" the information displayed is different. Duplicity would be if the entire contents of the line were the same. In this case, it is necessary to remove the "Feature" column from the result to ensure that for each CLIENT/CODE there is a single line.

Rarely DISTINCT is the correct way to eliminate repetitions; almost always there is some error in the FROM clause, in the junctions; either there is no column in the junction or there is an extra table.

When programming in SQL it is necessary to be attentive so that the predicates are sargable. Details in the article "Building Efficient T-SQL Code”.

Here is code suggestion that uses predicates sargable:

-- código #1
-- informe o ano a pesquisar
declare @ANO int;
set @ANO = 2019;

--
declare @Inicio_ano date, @Final_ano date, 
        @Inicio_ano_anterior date, @Final_ano_anterior date;
set @Inicio_ano= datefromparts (@ANO, 1, 1);
set @Final_ano= datefromparts (@ANO, 12, 31);
set @Inicio_ano_anterior= dateadd (year, -1, @Inicio_ano);
set @Final_ano_anterior= dateadd (year, -1, @Final_ano);

--
SELECT [Cliente] = T0.CardCode,
       [CODIGO] = T2.ItemCode,
       [Descricao] = T4.Descricao,
       [ANOAnterior] = (@ANO -1), 
       [ValorAnterior] = sum (coalesce (T3.LineTotal, 0)),
       [AnoAtual] = @ANO, 
       [ValorAtual] = sum (T2.LineTotal)

  from OINV as T0
       left join OINV as T1 on T0.CardCode = T1.CardCode 
       inner join INV1 as T2 on T0.DocEntry = T2.DocEntry 
       left join INV1 as T3 on T1.DocEntry = T3.DocEntry
       outer apply (SELECT top (1) T.Descricao
                      from dbo.ItemCaracteristica as T 
                      where T2.ItemCode = T.Item) as T4

  where T0.DocDate between @Inicio_ano and @Final_ano
        and T0.CANCELED = 'N'
        and (T1.CardCode is null or 
             (T1.DocDate between @Inicio_ano_anterior and @Final_ano_anterior))
        and T2.ItemCode = '1000'
        and (T3.DocEntry is null or T3.ItemCode = '1000')

  group by T0.Cardcode,T2.ItemCode, T4.Descricao;

---

In the author’s original code it is noticed that the tables OINV and INV1 appear twice; if the volume of lines to be read in the two years is high, it is better to change to read only once each table, to group also per year and to pivot with the result. Details in the article "Alas & Pivots”.

Answer 2

DECLARE
        @DATA INT

SET @DATA = 2019
SELECT 
       TB.Cliente,
       TB.CODIGO,
       TB.Descricao,
       GROUP_CONCAT(tb.Caracteristica ORDER BY tb.Caracteristica SEPARATOR '-'),
       TB.ANOAnterior,
       TB.ValorAnterior,
       TB.ANOATUAL,
       [ValorAtual] = SUM(TB.ValorAtual)


FROM (
        SELECT DISTINCT
            [Cliente] = T0.CardCode,
            [CODIGO] = T4.item,
            [Descricao] = T4.Descricao,
            [Caracteristica] = t4.caracteristica,
            [ANOAnterior] = YEAR(T1.DocDate), 
            [ValorAnterior] = T3.LineTotal,
            [AnoAtual] = YEAR(T0.DocDate), 
            [ValorAtual] = T2.LineTotal
            --CASE WHEN YEAR(T1.DocDate) IS NULL THEN 100 ELSE SUM(T0.DocTotal) / SUM(T1.DocTotal) END,
            --[CALCULO] = IIF(YEAR(T1.DocDate) IS NULL, 100 , SUM(T2.LineTotal) / SUM(T3.LineTotal))
        FROM OINV T0
        LEFT JOIN OINV T1 ON T0.CardCode = T1.CardCode AND YEAR(T1.DocDate) = @DATA -1
        INNER JOIN INV1 T2 ON T0.DocEntry = T2.DocEntry AND T2.ItemCode = '1000'
        LEFT JOIN  INV1 T3 ON T1.DocEntry = T3.DocEntry AND T3.ItemCode = '1000'
        INNER JOIN [DBO].[ItemCaracteristica] T4 ON T2.ItemCode = T4.Item
        WHERE YEAR(T0.DocDate) = @DATA
        AND T0.CANCELED = 'N'
--GROUP BY T0.Cardcode,T2.LineTotal, T3.LineTotal,YEAR(T1.DocDate), YEAR(T0.DocDate) 
) AS TB 
GROUP BY tb.Cliente, TB.CODIGO, TB.Descricao,
         TB.ANOAnterior, TB.AnoAtual, ValorAnterior

If I understood what you need it would be this, test there