CODEWITHSUNDEEP
mysqlsql
Akash khan
Akash khan

Reputation: 979

MySQL Opening closing balance with date range

I have a table transactions in my MySQL database with 50K records. The data looks like below.

trx_date    bill        due
2020-03-01  100.00      10.00
2020-03-02   50.00      20.00   
2020-03-02  100.00       0.00  
2020-03-03  200.00      30.00  
2020-03-04  100.00      10.00
...
...

My goal is to show the query result with query parameters from_date and to_date

from the starting date of table opening will be 0 and calculation will be

opening(+) bill (+) due (-) closing = ( opening+bill ) - due

trx_date    opening     bill        due     closing
2020-03-01  0           100.00      10.00    90.00
2020-03-02  90.00       50.00       20.00   120.00     
2020-03-02  120.0       100.00       0.00   220.00
2020-03-03  220.00      200.00      20.00   400.00  
2020-03-04  400.00      110.00      10.00   500.00
....
....

I can't figure out what is the optimal way to get the previous date closing for my selected from_date opening.

from_date=2020-03-03 and to_date=2020-03-04

trx_date    opening     bill        due     closing    
2020-03-03  220.00      200.00      20.00   400.00  
2020-03-04  400.00      110.00      10.00   500.00

Here my point is how I can get opening 220.00 from the previous result? For getting 220.00 from the previous result there could be thousands of records, so that what will be the best practice?

Upvotes: 1

Views: 2021

Answers (3)

Nick
Nick

Reputation: 147146

You need to compute all the opening and closing values starting from the first trx_date, and then filter the results by the dates you are interested in:

SELECT trx_date, opening, bill, due, closing
FROM (
  SELECT trx_date, 
         @balance AS opening,
         bill,
         due,
         @balance := @balance + bill - due AS closing
  FROM transactions
  CROSS JOIN (SELECT @balance := 0) ini
  ORDER BY trx_date
) tx
WHERE trx_date BETWEEN '2020-03-03' AND '2020-03-04'

Output:

trx_date    opening     bill    due     closing
2020-03-03  220         200     20      400
2020-03-04  400         110     10      500

Demo on dbfiddle

Upvotes: 2

Harun
Harun

Reputation: 1237

Try

SET @fromDate='2020-03-03', @toDate='2020-03-04';
SET @minDate = (SELECT MIN(trx_date) FROM transactions);
SET @previousDate=DATE_SUB(@fromDate,INTERVAL 1 DAY);

SELECT trx_date,@balance opening, bill, due, @balance := @balance + bill - due closing
FROM transactions, (SELECT @balance := (SELECT SUM(bill)-SUM(due) FROM transactions WHERE trx_date BETWEEN @minDate AND @previousDate)) variable
WHERE trx_date BETWEEN @fromDate AND @toDate ORDER BY trx_date;

Upvotes: 1

Akina
Akina

Reputation: 42622

Formally:

SELECT @balance opening, bill, due, @balance := @balance + bill - due closing
FROM source_table, (SELECT @balance := 0) variable
/* ORDER BY trx_date */
;

But there is no unique criteria in shown sample data - so the result is not deterministric.

Upvotes: 0

Related Questions