Opening Stock, Closing Stock By Date sql Query

Question

I have two table with primary key and foreign key (MaterialId)

Material Table (Multiple Material)

MaterialId   MaterialName   OpeningStock
1            Pen            100
2            Pencil         50

Material Stock (Multiple Material Entry)

MaterialId   PurchaseQty    SalesQty    Date
1            500            0           2016-12-15
1            0              0           2016-12-16
1            300            0           2016-12-17
1            0              400         2016-12-18
1            0              0           2016-12-19
1            0              0           2016-12-20
1            0              400         2016-12-21
1            200            100         2016-12-22

Now When I Pass @FromDate and @Todate I want to output like below:

Date         MaterialName   OpeningStock   PurchaseQty   SalesQty    ClosingStock
2016-12-15   Pen            100            500            0           600
2016-12-16   Pen            600            0              0           600
2016-12-17   Pen            600            300            0           900
2016-12-18   Pen            900            0              400         500
2016-12-19   Pen            500            0              0           500
2016-12-20   Pen            500            0              0           500
2016-12-21   Pen            500            0              400         100
2016-12-22   Pen            100            200            100         200

Note :
1. If Something is wrong on database tables so, please guide me how to handle this situation. 2. And Also find Current Date Stock From Two Tables

@KETULSONI please show us your query where you have tried something ? — Yogesh Sharma
1. Recursive CTE to generate list of dates 2. Cumulative sum — KETUL SONI

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2017-09-15T06:02:55

You are looking for a rolling sum of the various quantity values. One way to do this is using correlated subqueries:

SELECT
    t1.Date,
    mt.MaterialName,
    (SELECT OpeningStock FROM [Material Table] WHERE MaterialId = t1.MaterialId) +
    COALESCE((SELECT SUM(t2.PurchaseQty - t2.SalesQty) FROM [Material Stock] t2
     WHERE t2.Date < t1.Date AND t1.MaterialId = t2.MaterialId), 0) AS OpeningStock,
    t1.PurchaseQty,
    t1.SalesQty,
    (SELECT OpeningStock FROM [Material Table] WHERE MaterialId = t1.MaterialId) +
COALESCE((SELECT SUM(t2.PurchaseQty - t2.SalesQty) FROM [Material Stock] t2
     WHERE t2.Date <= t1.Date AND t1.MaterialId = t2.MaterialId), 0) AS ClosingStock
FROM [Material Stock] t1
INNER JOIN [Material Table] mt
    ON t1.MaterialId = mt.MaterialId
ORDER BY
    mt.MaterialName,
    t1.Date;

Note that it is bad table design to be storing the opening stock values in a separate table from the material stock table. This means the above query would return no pencil records. A better approach would be to insert a seed record into material stock, for each material, with the amount being the initial stock.

Output:

Demo here:

Opening Stock, Closing Stock By Date sql Query

2 Answers

Rextester