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;