Opening Stock, Closing Stock By Date sql Query

Viewed 6487

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

  1. Material Table (Multiple Material)
MaterialId   MaterialName   OpeningStock
1            Pen            100
2            Pencil         50
  1. 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

2 Answers
Related