Calculate Balance

• jsshivalik

  SSChasing Mays

  Points: 612

  More actions

  August 17, 2022 at 11:19 am

  #4077346

  Hi

  I have below query & want to display Balance also.

  Declare @frDate Date
  set @frDate = '2022/08/18'
  Declare @toDate Date
  set @toDate = '2022/08/18'
  Select T0.code,
  (select sum (Credit-Debit) from J1 where refdate <= @frDate and name = T0.Code)
  - (select Amount from Customer where code = T0.Code) 'Opening Balance',
  (Select sum(Amount) from order A0 where A0.code = T0.code and A0.date between @frDate and @toDate) 'Sales',
  (Select sum(value) from Pay A0 where A0.code = T0.code and A0.date between @frDate and @toDate) 'Payment'
  from order T0 inner join Customer t1
  on T0.code = t1.Code where 
  and T0.Date between @frDate and @toDate  
  group by T0.Code
  

  Thanks

• Site Owners

  SSC Guru

  Points: 80320

  More actions

  August 18, 2022 at 12:10 pm

  #4077872

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• pietlinden

  SSC Guru

  Points: 63428

  More actions

  August 18, 2022 at 3:17 pm

  #4077966

  Balance is basically a running total of deposits minus a running total of withdrawals. (Well, maybe starting with a monthly balance, because recalculating that all the time from the first date is computationally expensive).