Slow Moving Stock Report

Transact-SQL
1

Hi,

I'm trying to generate a slow moving stock report. Any current stocks that 5 years or older has to be classified as slow moving stock. I need to generate a report to show slow moving stocks.

The tables that I have:
Inventory Table

ProdID Quantity
1001 53
1002 32
1003 15

PurchaseOrders Table

POID OrderDate
2101 05-Jun-15
2102 08-Jan-16
2103 03-Sep-14
2104 05-Mar-16
2105 09-Jan-09
2106 04-Apr-11

PurchaseOrderDetails Table

PODID POID ProdID QuantityOrdered
3901 2101 1001 10
3902 2102 1001 20
3903 2103 1002 30
3904 2104 1002 40
3905 2105 1003 10
3906 2106 1003 5

In the example above, ProdID 1003 has 15pcs left and are move than 5 years old. It was purchased in:

9-Jan-2009 10pcs
4-Apr-2011 5pcs

So my report should show

ProdID Quantity Age (years)
1003 10pcs 8.2
1003 5pcs 6.0

Update: I've added an image to show the table design and desired output

882×448 72.6 KB

2

Don't you need to deduct Sales Qty from Purchase Order Qty to get "Current stock" - and then you need "Aged Stock" to know how old the oldest item is?

Or do you just want "Not re-ordered in the last 5 years"?

Maybe all you need is "Not SOLD in the last 5 years"?

3

There is no need to deduct sales qty from purchase qty because the current qty is stored in the Inventory table.

I think I missed out 1 important information. We work based on first in first out (FIFO) principle.

Example
Currently I have 15pcs of ProdID 1003
Below are the previous purchase orders for ProdID 1003
7 Sep 2008 - 10pcs
9 Jan 2009 - 10pcs
4 Apr 2011 - 5pcs

In total I had bought 25pcs. I currently have 15pcs left. I.e. I sold 10pcs
Based on FIFO principle, we assumed that the 10pcs sold was bought on 7 Sep 2008.
Therefore the remaining 15pcs are from 9 Jan 2009 and 4 Apr 2011.

The report I need is to retrieve slow moving stocks that are more than 5 years old.
Therefore I need to show the 10 pcs as 8.2 years old (from 2009) and 5pcs as 6.0 years old (from 2011).

4

Doh! Sorry, I missed that bit ...

... off out now, but if no one has answered later on I'll take another look

5

Hi all. Reposting to seek help.

Thanks

6

Let's start with some consumable test data which you should have provided:

CREATE TABLE #I
(
    ProdID int NOT NULL
    ,Quantity int NOT NULL
);
INSERT INTO #I
VALUES (1001, 53)
    ,(1002, 32)
    ,(1003, 15);

CREATE TABLE #PO
(
    POID int NOT NULL
    ,OrderDate date NOT NULL
);
INSERT INTO #PO
VALUES (2101, '20150605')
    ,(2102, '20160108')
    ,(2103, '20140903')
    ,(2104, '20160305')
    ,(2105, '20090109')
    ,(2106, '20110404');

CREATE TABLE #POD
(
    PODID int NOT NULL
    ,POID int NOT NULL
    ,ProdID int NOT NULL
    ,QuantityOrdered int NOT NULL
);
INSERT INTO #POD
VALUES (3901, 2101, 1001, 10)
    ,(3902, 2102, 1001, 20)
    ,(3903, 2103, 1002, 30)
    ,(3904, 2104, 1002, 40)
    ,(3905, 2105, 1003, 10)
    ,(3906, 2106, 1003, 5);

I am not quite sure what you want but this should get you started:

WITH UnknownOrderDates
AS
(
    SELECT I.ProdID, I.Quantity
        ,I.Quantity - SUM(POD.QuantityOrdered) AS QuantityOrdered
    FROM #I I
        JOIN #POD POD
            ON I.ProdID = POD.ProdID
    GROUP BY I.ProdID, I.Quantity
    HAVING SUM(POD.QuantityOrdered) < I.Quantity
)
,ProductsOrdered
AS
(
    SELECT ProdID, Quantity, QuantityOrdered, CAST(NULL AS date) AS OrderDate
    FROM UnknownOrderDates
    UNION ALL
    SELECT I.ProdID, I.Quantity, POD.QuantityOrdered, PO.OrderDate
    FROM #I I
        JOIN #POD POD
            ON I.ProdID = POD.ProdID
        JOIN #PO PO
            ON POD.POID = PO.POID
)
,ProductDateBalances
AS
(
    SELECT ProdID, Quantity, QuantityOrdered, OrderDate
        ,Quantity - SUM(QuantityOrdered)
            OVER (PARTITION BY ProdID ORDER BY OrderDate DESC ROWS UNBOUNDED PRECEDING) AS Balance
    FROM ProductsOrdered
)
SELECT ProdID
    ,CASE
        WHEN Balance >= 0
        THEN QuantityOrdered
        ELSE QuantityOrdered + Balance
    END AS Quantity
    ,CAST(ROUND(DATEDIFF(day, OrderDate, CURRENT_TIMESTAMP)/365.25, 1) AS decimal(9,1)) AS Age
FROM ProductDateBalances
WHERE Quantity + Balance >= 0
    AND (OrderDate <= DATEADD(year, -5, CURRENT_TIMESTAMP) OR OrderDate IS NULL);