SQLTeam.com | Weblogs | Forums

Slow Moving Stock Report

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

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"?

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).

Doh! Sorry, I missed that bit ...

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

Hi all. Reposting to seek help.

Thanks

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);