Get previous closed balance as open balance of date range

Question

I have three tables, products,stock or inwards,loading or outwards

so i want to to have sql query which will calculate the Closed balance from this Formular in date range

Closed balance= Open balance+inward-outward

For Example

products table

Id      ProductName OpenStock
1        Item 1      500
2        Item 1      40
3        Item 1      700
4        Item 1      80

Stock table(assume no stock in)

id   pid  qty   Indate
1    1     0    2020-04-28
2    2     0    2020-04-28
3    3     0    2020-04-28

Loading table

id   pid  qty     Outdate
1    1     30    2020-04-28
2    2     50    2020-04-28
3    3     30    2020-04-28

Am expecting that today Stock summary will be like this

Pid     OB     inward  outward   CB
  1     500      0        30    470
  2     400      0        50    350
  3     700      0        30    670
  ....

So tomorrow (next date) We expect that if there is no Inward or Ourwards quantity stock summary will be and so on:

  Pid     OB     inward  outward   CB
  1     470      0        30      470
  2     350      0        50      350
  3     670      0        30      670

What can i archive this stock summary, by sql query and what if i want to select from date to date

this is my sql query which return the screenshot below but does not works correct

    public function searchstockview(Request $request) {

       request()->validate([

        'startdate' =>'required',
        'enddate' => 'required',
       ]);
    $startdate=$request->startdate;
    $enddate=$request->enddate;
    $viewstockbydate= DB::select(DB::raw(" SELECT 
        products.id, 
        products.name,products.open_stock as open_balance,
        (select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id and 
       DATE(loadings.created_at)  BETWEEN STR_TO_DATE('$startdate','%m/%d/%Y') AND 
      STR_TO_DATE('$enddate','%m/%d/%Y')  ) as total_loadings_specific_date,

        (select categories.name from categories where categories.id=products.category_id) as 
       category_name,

        (select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and 
        DATE(stocks.created_at)  BETWEEN STR_TO_DATE('$startdate','%m/%d/%Y') AND 
        STR_TO_DATE('$enddate','%m/%d/%Y')  ) as total_stocks_specific_date
        from products"));
           }

keep in mind that is this query i takes open_balance,total_loadings_specific_date,total_stocks_specific_date and put in the formular above to get closed balance but this logic is not correct i need new ideas. how to resolve this

You're looking for a "running sum", essentially - what specific RDBMS are you using, because not all of them support window functions? Also, you're concatenating strings for your query, which will leave you open to SQL Injection: `STR_TO_DATE('$enddate','%m/%d/%Y')`. Further, the way you're doing date filtering will (probably) prevent the use of indices, and may give incorrect results: [don't use `BETWEEN`](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common). — Clockwork-Muse, Apr 28 '20 at 15:39
Ah, as Gordon points out, you likely don't need the running sum portion. For the rest, SQL Injection is prevented by [either using the query builders](https://stackoverflow.com/a/38514949/812837), or [by binding to the parameters later](https://stackoverflow.com/a/20873009/812837). Also, assuming your table actually has a date/time/timestamp type, you should probably convert the string to a date type in php. — Clockwork-Muse, Apr 28 '20 at 15:59

score 0 · Answer 1 · answered Apr 28 '20 at 15:37

0

On any given day, it seems that you can do:

select p.*, s.inward, l.outward,
       (p.openstock - coalesce(l.outward, 0) + coalesce(l.inward, 0)) as in_stock
from products p left join
     (select s.pid, sum(s.qty) as inward
      from stock s
      group by s.pid
     ) s
     on s.pid = p.id left join
     (select l.pid, sum(l.qty) as outward
      from loading l
      group by l.pid
     ) l
     on l.pid = p.id;

If you want to filter on a particular date in the past, add the filter in the subqueries.

answered Apr 28 '20 at 15:37

Gordon Linoff

1,242,037
58
646
786

do you mean i can use `where` or `where between` or can you update your code ? because i want to search by range date also – Emma Mfinanga Apr 28 '20 at 15:55
if you add `where` the data will not display – Emma Mfinanga Apr 28 '20 at 16:59

Get previous closed balance as open balance of date range

1 Answers1