Nested Select Query in SQL Server

Question

Hello everyone, I need assistance again in SQL Server. Please refer to my query and its output below:

Below also is the actual value of that table:

As you can see, there is a discrepancy in the previous day balance. Please help me to fix my query. Thank you so much.

Jefferson S. Motta · Answer

See what I fixed and add and replace in your code: 1. a.BankInformation = b.BankInformation 2. a .BankAccountNo = b.BankAccountNo ... FROM fin_Cash_FloMaster b WHERE a.BankInformation = b.BankInformation and a.BankAccountNo = b.BankAccountNo and b.TransactionDate = '2020-05-14' ... This should fix, best, Jeff

Jefferson S. Motta · Answer

You are wellcome.

Jes Sie · Answer

Dear Jeff, thank you.

Jes Sie · Answer

I'm sorry Jefferson but your suggested answer is as unclear as mine. The reason why I ask help is because obviously my query is wrong. So, I was asking if there could be some DBA out there who can help me formulate with the right query.

Jefferson S. Motta · Answer

It's because your column PreviouseBalance takes the same record in the clausule WHERE from b.TransactionDate = '2020-05-14'. If you didn´t like that you need set a new clause WHERE that will be valid for your needs.

Jes Sie · Answer

Hi Jefferson, that is actually the reason why I ask for help. The 50,000,000 previous day balance should only reflect to 1 row. What the first query did, is that it displays the 50M to all rows.

Jefferson S. Motta · Answer

THE PREVIOUS BALANCE IS THE SAME FOR ALL ROWS - I DONT KNOW IF THIS IS RIGHT FOR YOU???; THE 7 ROWS FROM RESULT 1 HAS THE CORRECT SUM VALUE, THE 11 ROWS ARE SUMMARIZED IN 7 ROWS. your SQL are returning the correct result, what I don't understood is why all ROWS have the same Previous Balance. if you take a calc and sum group by BankInformation you will check that is ok. Best, Jeff