CODEWITHSUNDEEP
mysqlsql
Arslan
Arslan

Reputation: 179

How to create a MySQL Query with accumulation on a created field?

I have two tables, Invoice and Receiving, and I am using MySQL. I want to generate a balance sheet from these two tables. The result should accumulate a balance over time based on the I_Total column from Invoice and the CR_Amount column from Receiving as shown in the figures below. I have tried many queries with different joins but I am not getting the desired output.

enter image description here

How can I achieve the desired balance sheet with a query or function in MySQL?

Upvotes: 0

Views: 1096

Answers (2)

spencer7593
spencer7593

Reputation: 108400

If we ignore the balance column for a moment, and assuming that the date column is DATE datatype, then getting the result shown can be achieved with something like this:

SELECT t.date
     , t.debit
     , t.credit
  FROM ( SELECT i.i_date       AS date
              , i.i_total      AS debit
              , 0              AS credit
              , 'i'            AS i_or_r
              , i.i_id         AS id
           FROM invoice i
          UNION ALL
         SELECT r.r_date       AS date
              , 0              AS debit
              , r.r_total      AS credit
              , 'r'            AS i_or_r
              , r.r_id         AS id
           FROM receiving r
       ) t
 ORDER
    BY t.date
     , t.i_or_r
     , t.id

To get the balance, we could do that processing on the client side, as the rows are retrieved.

NOTE: MySQL 8.0 introduces window functions, which have been available in other RDBMS such as SQL Server and Oracle (calls them "Analytic Functions").

Without window functions, to get it done in the SQL is going to be ugly.

We could make use of unsupported usage of user-defined variables. Using this approach, we would basically emulate the processing that we would do on the client side, fetching through the result that query (processing each row in order) to add/subtract from a "running balance" in a user-defined variable. The "ugly" part about this is that it relies on behavior that is not guaranteed. The MySQL Reference Manual includes warning about it.)

Or, to get the result using pure SQL, we could use a couple of complicated looking correlated subqueries to sum up the debit and credit amounts up to the current row, and do that for each row.

It looks like we are applying debits and credits towards the balance in a similar order that a bank does, applying all of the debits and credits in date order. And on each date, we apply the debits first, and then the credits.

From the sample data and expected result, it's not clear if debits are applied ordered by amount in ascending order, or by id in ascending order.

With the sample data, we get the same balance result either way. Assuming that i_id is unique in invoice, and cr_id is unique in receiving, we can get the balance by applying credits and debits using id order as a discriminator when we're at the current date.

(If we need to apply credits on the same date in ascending amount order, the subqueries would be a little more complicated, to take into account the possibility that two credits on the same date could be for the same amount.)

SELECT t.date
     , t.debit
     , t.credit

     , ( SELECT SUM(bi.i_total)
           FROM invoice bi
          WHERE bi.i_date <= t.date
            AND ( bi.i_date < t.date
                OR ( t.i_or_r = 'i' AND bi.i_id <= t.id )
                )
       )
     - ( SELECT SUM(br.cr_amount)
           FROM receiving br
          WHERE br.cr_date <= t.date
            AND ( br.cr_date < t.date
                OR ( t.i_or_r = 'r' AND br.cr_id <= t.id )
                OR t.i_or_r = 'i'
                )
       ) AS balance

  FROM ( SELECT i.i_date       AS date
              , i.i_total      AS debit
              , 0              AS credit
              , 'i'            AS i_or_r
              , i.i_id         AS id
           FROM invoice i
          UNION ALL
         SELECT r.cr_date      AS date
              , 0              AS debit
              , r.cr_amount    AS credit
              , 'r'            AS i_or_r
              , r.cr_id        AS id
           FROM receiving r
       ) t
 ORDER
    BY t.date
     , t.i_or_r
     , t.id

Upvotes: 2

Sam
Sam

Reputation: 110

Try This

SELECT *, SUM(Debit) OVER(ORDER BY dt,debit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)-SUM(Credit) OVER(ORDER BY dt,debit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) Balance
FROM (
SELECT ID,Dt,Amount AS Debit,0 AS Credit FROM Debit
UNION ALL
SELECT ID,Dt,0 AS Debit,Amount AS Credit FROM Credit
)X 
ORDER BY DT

DEMO

Upvotes: 0

Related Questions