Calcul de l'heure de fin de stock

La grande question est de savoir ce que vous voulez faire s'il y a une activité «achetée» alors qu'il y a encore du stock disponible. Comme vous n'avez pas de lien entre une vente et le stock, ce comportement est difficile à suivre. En supposant que cela ne se produise pas / vous ne vous souciez pas de ce cas, la solution est de simplement choisir le prochain stockage comme serre-livres comme suit

 SELECT ItemName, DateTime AS [Entry Time], Qty, CASE WHEN LEAD(Store.DateTime) OVER (ORDER BY Store.DateTime) IS NOT NULL THEN (SELECT MAX(DateTime) FROM Store s2 WHERE s2.StockInOutType = 2 AND s2.DateTime < LEAD(Store.DateTime) OVER (ORDER BY Store.DateTime)) ELSE NULL END AS EndingTime FROM Store WHERE Store.StockInOutType = 4 AND Store.ItemID = 25 ORDER BY DateTime 

Vous pouvez calculer le chiffre d'affaires de l'inventaire dans un model FIFO en calculant un numéro de série pour les points de départ et de sortie de chaque transaction d'achat.

Cela peut être effectué dans SQL Server en utilisant un total en cours d'exécution ( SUM(Qty) OVER (PARTITION BY ItemID ORDER BY TransactionDate ROWS UNBOUNDED PRECEDING) )

Exemple ( SqlFiddle ):

 -- Calculate serials for the orders and inventory receipts -- put them in temp tables to make them easier to work with. This could -- also be done with a view or a CTE CREATE TABLE #Orders ( TxnID INT NOT NULL, TxnDate DATETIME NOT NULL, ItemID INT NOT NULL, Qty INT NOT NULL, ConsumptionStartSerial INT, ConsumptionEndSerial INT ); INSERT INTO #Orders (TxnID, TxnDate, ItemID, Qty, ConsumptionStartSerial, ConsumptionEndSerial) SELECT TxnID, TxnDate, ItemID, Qty, ConsumptionEndSerial - Qty as ConsumptionStartSerial, ConsumptionEndSerial FROM ( SELECT TxnID, TxnDate, ItemID, ChangeQty * -1 as Qty, SUM(ChangeQty) OVER(PARTITION BY ItemID ORDER BY TxnID ROWS UNBOUNDED PRECEDING) * -1 as ConsumptionEndSerial FROM ItemTransactions WHERE ChangeQty < 0 ) as inr; SELECT * FROM #Orders; CREATE TABLE #InventoryReceipts ( TxnID INT NOT NULL, ItemID INT NOT NULL, Qty INT NOT NULL, StockStartSerial INT, StockEndSerial INT ); INSERT INTO #InventoryReceipts (TxnID, ItemID, Qty, StockStartSerial, StockEndSerial) SELECT TxnID, ItemID, Qty, StockEndSerial - Qty as StockStartSerial, StockEndSerial FROM ( SELECT TxnID, ItemID, ChangeQty as Qty, SUM(ChangeQty) OVER(PARTITION BY ItemID ORDER BY TxnID ROWS UNBOUNDED PRECEDING) as StockEndSerial FROM ItemTransactions WHERE ChangeQty > 0 ) as inr; SELECT * FROM #InventoryReceipts; -- Stock turnover -- Find the first and last order for each inventory receipt -- Calculate the days on shelf based off of the first and last -- order which uses the product SELECT inr.TxnID as StockTxn, txn.TxnDate, txn.[Description], inr.ItemID, inr.Qty, i.Name, inr.StockOpenedTxnID, inr.StockFinishedTxnID, DATEDIFF(day, txn.TxnDate, oStart.TxnDate) as DaysToOpen, DATEDIFF(day, txn.TxnDate, oEnd.TxnDate) as DaysToConsumed FROM ( SELECT ir.TxnID, ir.ItemID, ir.Qty, MAX(oStart.TxnID) as StockOpenedTxnID, MIN(oEnd.TxnID) as StockFinishedTxnID FROM #InventoryReceipts ir LEFT OUTER JOIN #Orders oStart on ir.ItemID = oStart.ItemID and ir.StockStartSerial >= oStart.ConsumptionStartSerial LEFT OUTER JOIN #Orders oEnd on ir.ItemID = oEnd.ItemID and ir.StockEndSerial < oEnd.ConsumptionEndSerial GROUP BY ir.TxnID, ir.ItemID, ir.Qty ) as inr INNER JOIN Items i on inr.ItemID = i.ItemID INNER JOIN ItemTransactions txn on inr.TxnID = txn.TxnID LEFT OUTER JOIN #Orders oStart ON inr.StockOpenedTxnID = oStart.TxnID LEFT OUTER JOIN #Orders oEnd ON inr.StockFinishedTxnID = oEnd.TxnID DROP TABLE #InventoryReceipts; DROP TABLE #Orders; 

Résultats :

 | StockTxn | TxnDate | Description | ItemID | Qty | Name | StockOpenedTxnID | StockFinishedTxnID | DaysToOpen | DaysToConsumed | |----------|---------------------------|---------------------------|--------|-----|---------|------------------|--------------------|------------|----------------| | 1 | January, 01 2015 00:00:00 | Received 10 from supplier | 1 | 10 | Chicken | 3 | 6 | 8 | 12 | | 2 | January, 02 2015 00:00:00 | Received 5 from supplier | 2 | 5 | Beef | 5 | (null) | 9 | (null) | | 4 | January, 11 2015 00:00:00 | Received 5 from supplier | 1 | 5 | Chicken | 6 | (null) | 2 | (null) |