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

Hello Damian,

This is Ethan. You could separate sales for each location using sub queries with an OUTER JOIN.

If you need custom solutions such as Advanced SQL Queries, Rules Engine, Custom Reports, API Solutions or Integrations, please feel free to reach me at ethan@hypercommerce.co.uk