Share via

Correct SQL query to not repit articles

Javier Prieto 1 Reputation point
2024-10-24T18:39:13.1333333+00:00

Hello, good afternoon, I have the following query in SQL and it is returning duplicate items. I managed to decipher that the items that are duplicated in the "On Orders" column in one row appear with one quantity and in the next row (Same item, the one that was duplicated) it has another quantity and I cannot decipher why. I would appreciate it if you could help me.

DECLARE @intBrand_ID AS INTEGER;

DECLARE @intItem_ID AS INTEGER;

SET @intBrand_ID = -1;

SET @intItem_ID = -1;

DECLARE @depoCorr AS INTEGER = 1;

DECLARE @depoWeb AS INTEGER = 33;

DECLARE @depoBravard AS INTEGER = 2;

DECLARE @depoTranster AS INTEGER = 6;

DECLARE @depoDISA AS INTEGER = 44;

DECLARE @depoML AS INTEGER = 45;

DECLARE @depoAgrelo AS INTEGER = 46;

DECLARE @catHerramientas AS INTEGER = 46;

-- DepoCorr

SELECT item_id, SUM(CAST(itst_cant AS INT)) AS Cantidad, itst_PickingLocation AS Ubicacion

INTO #DepoCorr

FROM tbItemStorage

WHERE stor_id = 1

GROUP BY item_id, itst_PickingLocation;

-- DepoBravard

SELECT item_id, SUM(CAST(itst_cant AS INT)) AS Cantidad, itst_PickingLocation AS Ubicacion

INTO #DepoBravard

FROM tbItemStorage

WHERE stor_id = 2

GROUP BY item_id, itst_PickingLocation;

-- DepoWeb

SELECT item_id, SUM(CAST(itst_cant AS INT)) AS Cantidad, itst_PickingLocation AS Ubicacion

INTO #DepoWeb

FROM tbItemStorage

WHERE stor_id = 33

GROUP BY item_id, itst_PickingLocation;

-- DepoTranster

SELECT item_id, SUM(CAST(itst_cant AS INT)) AS Cantidad

INTO #DepoTranster

FROM tbItemStorage

WHERE stor_id = 6

GROUP BY item_id;

-- DepoDISA

SELECT item_id, SUM(CAST(itst_cant AS INT)) AS Cantidad

INTO #DepoDISA

FROM tbItemStorage

WHERE stor_id = 44

GROUP BY item_id;

-- DepoML

SELECT item_id, SUM(CAST(itst_cant AS INT)) AS Cantidad

INTO #DepoML

FROM tbItemStorage

WHERE stor_id = 45

GROUP BY item_id;

-- DepoAgrelo

SELECT item_id, SUM(CAST(itst_cant AS INT)) AS Cantidad

INTO #DepoAgrelo

FROM tbItemStorage

WHERE stor_id = 46

GROUP BY item_id;

-- Preparacion (Aseguramos que esté agrupada por item_id correctamente)

SELECT tsod.item_id AS item_ID, tsod.sod_itemDesc, SUM(tsod.sod_qty) AS it_qty

INTO #Preparacion

FROM tbSaleOrderHeader AS tsoh

INNER JOIN tbSaleOrderDetail AS tsod ON tsoh.bra_id = tsod.bra_id AND tsoh.soh_id = tsod.soh_id

WHERE tsoh.ssos_id IN (20, 50)

GROUP BY tsod.item_id, tsod.sod_itemDesc;

-- Consulta final

SELECT DISTINCT

ti.item_vendorCode AS 'Cod_Fabricante',

tc.cat_desc AS 'Categoria',

tsc.subcat_desc AS 'Subcategoria',

tb.brand_desc AS 'Marca',

ti.item_codeAlternative AS 'Cod_Adicional',

ti.item_id AS 'ID',

ti.item_code AS 'Codigo',

ti.item_desc AS 'Articulo',

CAST(ISNULL(#Preparacion.it_qty, 0) AS DECIMAL(10, 1)) AS 'En Pedidos', -- Corregido

CAST(tpli.prli_price AS DECIMAL(19, 2)) AS 'Precio_Vta',

CAST(ticl.coslis_price AS DECIMAL(19, 2)) AS 'Costo',  

CAST(

    ISNULL(#DepoCorr.Cantidad, 0)

    + ISNULL(#DepoBravard.Cantidad, 0)

    + ISNULL(#DepoWeb.Cantidad, 0)

    + ISNULL(#DepoTranster.Cantidad, 0)

    + ISNULL(#DepoDISA.Cantidad, 0)

    + ISNULL(#DepoML.Cantidad, 0)

    + ISNULL(#DepoAgrelo.Cantidad, 0)

    AS DECIMAL(10, 1)

) AS 'Total capital',

dbo.fnGetAStock(1, @depoCorr, ti.item_id, 999999999) 

+ dbo.fnGetAStock(1, @depoBravard, ti.item_id, 999999999)

+ dbo.fnGetAStock(1, @depoTranster, ti.item_id, 999999999)

+ dbo.fnGetAStock(1, @depoWeb, ti.item_id, 999999999)

+ dbo.fnGetAStock(1, @depoDISA, ti.item_id, 999999999)

+ dbo.fnGetAStock(1, @depoML, ti.item_id, 999999999)

+ dbo.fnGetAStock(1, @depoAgrelo, ti.item_id, 999999999) AS 'Total capital potencial'

, CAST(ISNULL(#DepoCorr.Cantidad, 0) AS DECIMAL(10,1)) AS '01.Corrientes FS'

, dbo.fnGetAStock (1, @depoCorr, ti.item_id,999999999)  AS '01.Corrientes PS'

, CAST(ISNULL(#DepoWeb.Cantidad, 0) AS DECIMAL(10,1)) AS '06.Web FS'

, dbo.fnGetAStock (1, @depoWeb, ti.item_id,999999999) AS '06.Web PS'		

, CAST(ISNULL(#DepoBravard.Cantidad, 0) AS DECIMAL(10,1)) AS '02.Bravard FS'

, dbo.fnGetAStock (1, @depoBravard, ti.item_id,999999999) AS '02.Bravard PS'

, CAST(ISNULL(#DepoTranster.Cantidad, 0) AS DECIMAL(10,1)) AS '08.Transter FS'

, dbo.fnGetAStock (1, @depoTranster, ti.item_id,999999999) AS '08.Transter PS'	

, CAST(ISNULL(#DepoDISA.Cantidad, 0) AS DECIMAL(10,1)) AS '24.Depósito DISA FS'

, dbo.fnGetAStock (1, @depoDISA, ti.item_id,999999999) AS '24.Depósito DISA PS'

, CAST(ISNULL(#DepoML.Cantidad, 0) AS DECIMAL(10,1)) AS '25.Depósito ML FS'

, dbo.fnGetAStock (1, @depoML, ti.item_id,999999999) AS '24.Depósito ML PS'    

, CAST(ISNULL(#DepoAgrelo.Cantidad, 0) AS DECIMAL(10,1)) AS '26.Depósito Agrelo FS'

, dbo.fnGetAStock (1, @depoAgrelo, ti.item_id,999999999) AS '24.Depósito Agrelo PS'    

, ((ISNULL(#DepoCorr.Cantidad, 0)

	+ ISNULL(#DepoBravard.Cantidad, 0) 		

	+ ISNULL(#DepoWeb.Cantidad, 0) 

	+ ISNULL(#DepoTranster.Cantidad, 0) 		

	+ ISNULL(#DepoDISA.Cantidad, 0) 

	+ ISNULL(#DepoML.Cantidad, 0) 

	+ ISNULL(#DepoAgrelo.Cantidad, 0)) * CAST(tpli.prli_price AS DECIMAL(19, 2))) AS 'Cant. x Precio'

, tscurA.curr_desc AS 'Moneda precio de venta'

, ((ISNULL(#DepoCorr.Cantidad, 0) 

	+ ISNULL(#DepoBravard.Cantidad, 0) 		

	+ ISNULL(#DepoWeb.Cantidad, 0) 

	+ ISNULL(#DepoTranster.Cantidad, 0) 		

	+ ISNULL(#DepoDISA.Cantidad, 0) 

	+ ISNULL(#DepoML.Cantidad, 0) 

	+ ISNULL(#DepoAgrelo.Cantidad, 0)) * CAST(ticl.coslis_price AS DECIMAL(19, 2))) AS 'Cant. x Costo'

, tscurB.curr_desc AS 'Moneda del costo'

, ti.item_upb AS 'Unidades x bulto'

FROM tbItem AS ti

INNER JOIN tbItemCostList AS ticl ON ticl.item_id = ti.item_id AND ticl.coslis_id = 1

INNER JOIN tbsysCurrency AS tscurB ON ticl.curr_id = tscurB.curr_id

LEFT JOIN tbPriceListItems AS tpli ON ti.item_id = tpli.item_id AND tpli.prli_id = 1

LEFT JOIN tbsysCurrency AS tscurA ON tpli.curr_id = tscurA.curr_id

LEFT JOIN tbSubCategory AS tsc ON ti.subcat_id = tsc.subcat_id

LEFT JOIN tbCategory AS tc ON ti.cat_id = tc.cat_id

LEFT JOIN tbBrand AS tb ON ti.brand_id = tb.brand_id

LEFT JOIN #DepoCorr ON #DepoCorr.item_id = ti.item_id

LEFT JOIN #DepoBravard ON #DepoBravard.item_id = ti.item_id

LEFT JOIN #DepoWeb ON #DepoWeb.item_id = ti.item_id

LEFT JOIN #DepoTranster ON #DepoTranster.item_id = ti.item_id

LEFT JOIN #DepoDISA ON #DepoDISA.item_id = ti.item_id

LEFT JOIN #DepoML ON #DepoML.item_id = ti.item_id

LEFT JOIN #DepoAgrelo ON #DepoAgrelo.item_id = ti.item_id

LEFT JOIN #Preparacion ON #Preparacion.item_ID = ti.item_id -- Corregido

WHERE

(

    (tpli.prli_id = 1 AND ticl.coslis_id = 1 AND ISNULL(#DepoCorr.Cantidad, 0) > 0) OR

    (tpli.prli_id = 1 AND ticl.coslis_id = 1 AND ISNULL(#DepoBravard.Cantidad, 0) > 0) OR        

    (tpli.prli_id = 1 AND ticl.coslis_id = 1 AND ISNULL(#DepoTranster.Cantidad, 0) > 0) OR        

    (tpli.prli_id = 1 AND ticl.coslis_id = 1 AND ISNULL(#DepoDISA.Cantidad, 0) > 0) OR

    (tpli.prli_id = 1 AND ticl.coslis_id = 1 AND ISNULL(#DepoML.Cantidad, 0) > 0) OR

    (tpli.prli_id = 1 AND ticl.coslis_id = 1 AND ISNULL(#DepoWeb.Cantidad, 0) > 0) OR

    (tpli.prli_id = 1 AND ticl.coslis_id = 1 AND ISNULL(#DepoAgrelo.Cantidad, 0) > 0)

)

AND ((@intBrand_ID = -1 AND 1 = 1) OR (tb.brand_id = @intBrand_ID))

AND ((@intItem_ID = -1 AND 1 = 1) OR (ti.item_id = @intItem_ID))

AND ti.cat_id = @catHerramientas

ORDER BY Categoria, Subcategoria, Articulo;

DROP TABLE #DepoCorr;

DROP TABLE #DepoBravard;

DROP TABLE #DepoWeb;

DROP TABLE #DepoTranster;

DROP TABLE #DepoDISA;

DROP TABLE #DepoML;

DROP TABLE #DepoAgrelo;

DROP TABLE #Preparacion;

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,214 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
108 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,670 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 114.6K Reputation points MVP
    2024-10-24T21:25:12.3966667+00:00

    The piece of SQL code is long and complicated and we have no knowledge of the tables.

    However, I made an observation. You have a number of LEFT JOINs like this:

    LEFT JOIN #DepoCorr ON #DepoCorr.item_id = ti.item_id
LEFT JOIN #DepoBravard ON #DepoBravard.item_id = ti.item_id
LEFT JOIN #DepoWeb ON #DepoWeb.item_id = ti.item_id
...

    This works well, if every item_id can hit at most one row in the temp table.

    However, I see that you fill them up like this:

    SELECT item_id, SUM(CAST(itst_cant AS INT)) AS Cantidad, itst_PickingLocation AS Ubicacion
INTO #DepoCorr
FROM tbItemStorage
WHERE stor_id = 1
GROUP BY item_id, itst_PickingLocation;
-- DepoBravard
SELECT item_id, SUM(CAST(itst_cant AS INT)) AS Cantidad, itst_PickingLocation AS Ubicacion
INTO #DepoBravard
FROM tbItemStorage
WHERE stor_id = 2
GROUP BY item_id, itst_PickingLocation;

    That is, an item_id can match many rows if there are multiple picking locations.

    Say that for the first join, there are three rows in #DepoCorr for item_id = 123. There are four in #DepoBravard, two in #DepoWeb. That will produce 234 = 24 rows. That is, you get all combinations of picking lists. And this was only these three temp tables.

    Since I don't know your tables or business rules, I can't say what is the right solution. What I can say is that the above query makes little sense, and you need to think more about what you want to achieve. But maybe it is as simple as removing the PickingLocation from the queries that fill the temp table. I can't see that you are actually using those Ubicacion columns.

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.