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

Moovi Rastogi 1 Reputation point
2021-02-19T18:36:56.29+00:00

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

  1. VoucherID
  2. VoucherDate
  3. AccountID
  4. VoucherAmount
  5. VoucherType (Deposit/Withdrawal)
  6. 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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Tom Cooper 8,461 Reputation points
    2021-02-19T21:03:07.057+00:00

    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

    0 comments No comments