Sold Statistics duplicates Script

Hi,

Working on script for sold statistics but because I some  products are two locations I have duplicate SKU in my final result . 

Tried to use DISTINCT but it does not work form me well. 

Need to have it without duplicated SKU. Anyone?

Thanks

Damian

  

DECLARE @Supplier NVARCHAR(255) = ''

SELECT DISTINCT si.ItemNumber AS SKU,
si.ItemTitle AS 'Title',
iloc.BinRackNumber AS 'Location (bin/rack)',
sl.Quantity AS 'Stock Level',
sup.SupplierName AS Supplier

FROM StockItem si
INNER JOIN ItemSupplier isup on isup.fkStockItemId = si.pkStockItemId
INNER JOIN Supplier sup on sup.pkSupplierID = isup.fkSupplierId 
INNER JOIN PackageGroups pg on pg.PackageCategoryID= si.PackageGroup
LEFT JOIN StockLevel sl on sl.fkStockItemId = si.pkStockItemId AND sl.fkStockLocationId =  '00000000-0000-0000-0000-000000000000'
LEFT JOIN StockLevel slo on slo.fkStockItemId = si.pkStockItemId AND slo.fkStockLocationId =  '00000000-0000-0000-0000-000000000000'
LEFT JOIN ItemLocation iloc on iloc.fkStockItemId = si.pkStockItemId
INNER JOIN ProductCategories cat on cat.CategoryId = si.CategoryId
LEFT JOIN StockItems sis on sis.fkStockControlStockItemId = si.pkStockItemId
LEFT JOIN OrderItem oi on oi.fkStockId = sis.pkStockID
LEFT JOIN [Order] o on o.pkOrderId = oi.fkOrderId

WHERE si.bLogicalDelete = 0 AND (sup.SupplierName = @Supplier OR @Supplier = '')
GROUP BY si.ItemNumber,
isup.SupplierCode,
si.ItemTitle,
sl.Quantity,
iloc.BinRackNumber,
sup.SupplierName

  


Login to post a comment