CODEWITHSUNDEEP
sqlsql-server
Umair Mubeen
Umair Mubeen

Reputation: 843

How to calculate balance in SQL Server?

I have a Query which Result is 

Code      id    Date        Amount  Paid      Balance
------------------------------------------------------
INV-000001  0   10/25/2019  50000   420       0
INV-000001  0   10/25/2019  50000   90        0
INV-000001  0   10/25/2019  50000   100       0
INV-000002  0   10/30/2019  25000   0         0
INV-000003  0   10/30/2019  25000   0         0
INV-000004  0   10/30/2019  25000   0         0

I want to calculate the balance in SQL. e.g.,

in 1st row, the amount is 50000 and paid 420 so balance is 49580. In second-row balance will be 50000 - 90 -(first-row balance 420) = 49490

SELECT p.Code
    ,p.DATE
    ,p.STATUS
    ,'' AS Customer
    ,InvoiceAmount
    ,ISNULL(Paid, 0) AS Paid
    ,0 AS balance
FROM purchaseinvmaster p
LEFT OUTER JOIN InvoiceVoucherMap i ON i.InvoiceId = p.id
LEFT OUTER JOIN debitcreditmaster d ON i.VoucherId = d.id

Upvotes: 0

Views: 1881

Answers (3)

Eric Brandt
Eric Brandt

Reputation: 8101

Based on the ISNULL() in the base query, I'm assuming you're working on SQL Server, but this should work with minor tweaks on most major platforms.

I'm further assuming that the DATE column is just a date, not a datetime. That means we need to impose an order on the transactions that occur on the same date. To do that, I took your base query and wrapped it in a Common Table Expression (CTE) and added a simple ROW-NUMBER() to the results. One of your tables might have a column that suggests a better order, but without one, this will do. It uses the clause ORDER BY (SELECT 0) because the window function requires an order by, but we don't have an ordering column to reference.

After that, in the main query, use a windowed SUM to get your running total of payments.

WITH cte AS
(
  SELECT 
     p.Code
    ,p.DATE
    ,p.STATUS
    ,'' AS Customer
    ,InvoiceAmount
    ,ISNULL(Paid, 0) AS Paid
    ,ROW_NUMBER() OVER (PARTITION BY p.Code ORDER BY (SELECT 0)) AS OrdinalNbr
  FROM purchaseinvmaster p
  LEFT OUTER JOIN InvoiceVoucherMap i
    ON i.InvoiceId = p.id
  LEFT OUTER JOIN debitcreditmaster d
    ON i.VoucherId = d.id
)
  SELECT 
     Code
    ,DATE
    ,STATUS
    ,Customer
    ,InvoiceAmount
    ,Paid
    ,Balance = InvoiceAmount - (SUM(Paid) OVER (PARTITION BY Code ORDER BY OrdinalNbr))
  FROM 
    cte;

Upvotes: 0

Owais
Owais

Reputation: 106

SELECT  p.Code, 
         InvoiceAmount , ISNULL(Paid,0) as Paid,
        InvoiceAmount - SUM(ISNULL(Paid,0)) OVER (ORDER BY p.id ROWS UNBOUNDED PRECEDING) AS Balance
from purchaseinvmaster p

Upvotes: 3

The Impaler
The Impaler

Reputation: 48750

You don't mention which database you are using, but you can use a standard window function to compute the balance. For example:

select
  *,
  amount - sum(paid) over(order by date) as balance
from t

Upvotes: 1

Related Questions