Getting sums from another table on conditions in query for every row

Question

Dear Friends,

I have two tables with following structures:

Account Table

1. AccountID
2. CustomerID
3. OpeningDate
4. AccountNo
5. OpeningBalance
6. AccountStatus

Voucher Table

7. VoucherID
8. VoucherDate
9. AccountID
10. VoucherAmount
11. VoucherType (Deposit/Withdrawal)
12. VoucherStatus

Now i need to create a query to show following:

• VoucherID
• VoucherDate
• AccountID
• Deposit
• Withdrawal
• Balance
• VoucherStatus

I am able to show all details but balance is showing only current balance for all vouchers if an account have multiple vouchers. I want to show the immediate balance after every voucher.

So, requesting you all to help me out.

Thanks

Moovi Rastogi

Answer 1

When you are looking for help on creating a SQL statement(s), it is best if you give us your tables and some sample data (in the form of CREATE TABLE and INSERT statements. Then show us the result you would want from that sample data. Also tell us which release of SQL Server you are using. And, if you have a query that you've developed that doesn't work, show us that query and if you are getting errors, show us the error you are getting with that query.

All of that will help us to understand what it is you really want and lets us quickly develop a tested solution that will run on the release of SQL Server you are using.

But I think what you want is something like (I left out some columns in your tables that I don't think you need to use in the query).

Create Table Account(AccountID int, OpeningBalance decimal(9,2));
Create Table Voucher(VoucherID int, VoucherDate Date, AccountID int, VoucherAmount decimal(9,2), VoucherType varchar(20), /*(Deposit/Withdrawal)*/ VoucherStatus int);

Insert Account Values 
(1, 25.00), 
(3, 123.45);

Insert Voucher Values
(1, '20210101', 1, .23, 'Deposit', 1),
(2, '20210103', 1, 5.00, 'Withdrawal', 1),
(4, '20210102', 1, 7.23, 'Deposit', 1),
(3, '20210104', 3, 1237.23, 'Deposit', 1);

Select v.VoucherID, v.VoucherDate, v.AccountID, v.VoucherAmount, 
  Case When v.VoucherType = 'Deposit' Then v.VoucherAmount Else 0 End As Deposit,
  Case When v.VoucherType = 'Withdrawal' Then v.VoucherAmount Else 0 End As WithDrawal,
  a.OpeningBalance + Sum(Case When v.VoucherType = 'Deposit' Then v.VoucherAmount Else -1 * v.VoucherAmount End) 
    Over(Partition By v.AccountID Order By v.VoucherDate, VoucherID)
From Account a
Inner Join Voucher v On a.AccountID = v.AccountID
Order By v.AccountID, v.VoucherDate, v.VoucherID;

Tom