1 Replies - 761 Views - Last Post: 27 June 2013 - 10:04 PM Rate Topic: -----

#1 kollypap  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 05-January 11

subtract two summed column and add it to another two summed column

Posted 27 June 2013 - 04:07 PM

I have a single table which i want to use a single SQL statement from Visual basic 6 platform to Select and subtract summed column based on where clause. Below is the Description Here is a Sample Table to explain better:

ID Deposit Withdrawal Date
ACC01 1000 0 10/10/2012
ACC01 2000 0 1/1/2013
ACC02 3000 0 10/10/2012
ACC02 4000 0 1/1/2013
ACC01 0 1000 12/12/2012
ACC02 0 3000 12/12/2012

I want to sum values in deposit column where date less than 1/1/2013 as DepositBefore Also sum values in withdrawal column where date less than 1/1/2013 as WithDrawalBefore then subtract WithdrawalBefore from DepositBefore (i.e. BalanceBefore = DepositBefore - WithrawalBefore)

In the same vein, sum values in deposit column where date >= 1/1/2013 as DepositAfter Also sum values in Withdrawal column where date >= 1/1/2013 as WithdrawalAfter so that (BalanceAfter = DepositAfter - WithdrawalAfter)

Finally compute BalanceForInterest = BalanceBefore + BalanceAfter. Would prefer if a single query can resolve this Thanks in Advance

Below is the code i tried but did not succeed, it recieved "Unspecified Error".
With rsSaving
.Open "SELECT" _
    & "(SELECT SUM(Deposit - Withdrawal) AS Total FROM tblSaving WHERE StaffID = 'ACA001' AND TransactDate < #01/01/2013#) " _
    & " + (SELECT SUM(Withdrawal) AS Tot FROM tblSaving WHERE StaffID = 'ACA001' AND TransactDate >= #01/01/2013#) " _
    & "AS BalanaceAfter", conDB, adOpenDynamic, adLockOptimistic
.close
End With
set rsSaving = Nothing


rsSaving is an ADO object, while conDB is my connection. Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: subtract two summed column and add it to another two summed column

#2 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: subtract two summed column and add it to another two summed column

Posted 27 June 2013 - 10:04 PM

Well here i tried to use subquery
select isnull(a.deposit-a.Withdrawal)AS Total,b.Withdrawal FROM (select sum(deposit),sum(Withdrawal)from tblSaving where StaffID = 'ACA001' AND TransactDate < #01/01/2013# ) as a full outer join(SELECT SUM(Withdrawal) AS Tot FROM tblSaving WHERE StaffID = 'ACA001' AND TransactDate >= #01/01/2013#) as b on a.ID=B.ID


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1