Would anyone happen to have a custom script for 'Consumption Rate - Based on Availability' but with the option to search for 'ALL' suppliers instead of a specific supplier?

Hello Warren,

This is Ethan from HyperCommerce (ethan@hypercommerce.co.uk). I'm able to do this for you, But what happens if you no supplier information / have two different suppliers for the same product? 

This will result in a duplicate dataset and you will have a hard time filtering out SKUs which have no supplier information (i.e only Default Supplier). It is possible that you might omit these from your report, if you filter out all SKUs with Supplier = Default.

Unless, you are 100% sure that your supplier information is accurate in your Linnworks inventory AND You don't have more than 1 supplier for a particular product, the below query will not work for you, as it will display duplicate data. This is because, Linnworks only knows which SKU is sold and processed, it is unable to track whether the SKU that was sold was ordered from Supplier 1 / Supplier 2 and so on.

Again, I could be wrong as it is quite possible that Linnworks tracks the Inventory using FIFO and is maybe able to track Supplier after all, but if you also make manual adjustments to Stock Items (to put items in and out of stock), I'm guessing Linnworks will switch back to the default supplier / the first supplier in the list when the stock is updated next time.

In short, the below query will display duplicate results most likely, if you have a 'Default' supplier within your inventory

Query

-- Query: Consumption Rate for all Supplier Items
-- Author: Ethan from HyperCommerce (ethan@hypercommerce.co.uk)
-- If you need custom solutions such as Advanced SQL Queries, Custom Reports, 
-- API Solutions or Integrations, please feel free to reach me at ethan@hypercommerce.co.uk


DECLARE @NumDays DECIMAL,
        @Location NVARCHAR(MAX);

------------------------------------------------------------------------------------------------
-- Please specify the duration for the report in days. For ex: 30
-- You need to specify the Location Name for the report as well, if you have multiple Locations,
-- please run the report again with a different Location Name.

SET @NumDays = 30;
SET @Location = 'Default';
------------------------------------------------------------------------------------------------

SELECT 
    *
FROM 
    (SELECT
        s.ItemNumber AS [SKU],
        BarcodeNumber AS [Barcode],
        SupplierName,
        SupplierCode,
        SupplierBarcode,
        ItemTitle,
        KnownPurchasePrice,
        s.TaxRate,
        IIF(ISNULL(nQty, 0) = 0, 0, SUM(nQty)) AS [SoldQty],
        lv.Quantity AS [Available],
        IIF(ISNULL(nQty, 0) = 0, 0, CAST(SUM(nQty)/@NumDays AS DECIMAL(10,2))) AS [DailyConsumptionRate],
        OnOrder,
        BinRackNumber AS [BinRack]
    FROM
        [StockItem] s
    FULL OUTER JOIN
        [OrderItem] oi ON s.pkStockItemid = oi.fkStockItemId_Processed
    LEFT JOIN
        [Order] o ON oi.fkOrderid = o.pkOrderId AND dProcessedOn > DATEADD(day, -@NumDays, GETDATE())
    LEFT JOIN
        [ItemSupplier] si ON si.fkStockItemId = s.pkStockItemId
    LEFT JOIN
        [Supplier] su ON su.pkSupplierId = si.fkSupplierId
    LEFT JOIN
        [StockLevel] lv ON lv.fkStockItemId = s.pkStockItemId
    LEFT JOIN
        [StockLocation] sl ON sl.pkStockLocationId = lv.fkStockLocationId
    LEFT JOIN
         [ItemLocation] il ON il.fkStockItemId = s.pkStockItemId
    WHERE
        Location = @Location
    GROUP BY 
        s.ItemNumber,
        nQty,
        BarcodeNumber,
        SupplierName,
        SupplierCode,
        SupplierBarcode,
        ItemTitle,
        KnownPurchasePrice,
        s.TaxRate,
        lv.Quantity,
        OnOrder,
        BinRackNumber) q1

Notes:

You need to specify two values for the query to work (See screenshot below)