0 Replies - 460 Views - Last Post: 27 June 2013 - 04: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:04 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= #1/1/2013# GROUP BY ID)",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
  • +

Page 1 of 1