SQL Server, SQL Query Assistance Required

Question

Table 1 : Employee

    EmpId  CreatedAt
    --------------------------
    100    2015-11-09 07:21:02
    200    2017-01-24 18:24:01
    300    2016-08-20 06:55:35

Table 2 : Account

    AccId   EmpID   Currency   CreatedAt
    -----------------------------------------------
    9000     100       USD     2017-04-20 19:40:55
    9001     200       USD     2017-04-20 19:40:55
    9002     100       EUR     2017-05-20 19:40:55
    9003     200       USD     2017-04-20 19:40:55
    9004     100       USD     2017-04-20 19:40:55

Table 3 : Transaction

  TrnsId   AccId Amount CreatedAt
  ------------------------------------------
  10       9000   3000  2017-04-25 19:40:55
  11       9001    500  2017-05-25 19:40:55
  12       9000   -200  2017-05-30 19:40:55
  13       9000   -500  2017-06-11 19:40:55

I need to calculate the ending balance for the account after each transaction entry. Assume that each account begins with a zero balance.

I have written this query:

SELECT 
    account.AccId, SUM(transaction.amount) AS [Ending Balance] 
FROM 
    account
JOIN 
    transaction ON transaction.AccId = account.AccId 
GROUP BY 
    account.AccId

but the results returned by the query are not correct.

How do you know the query is not correct? Errors? (post the message), Incorrect results? (Post what you get and what you want) — user1443098, Sep 19 '18 at 18:30
With *"after each transaction entry"* do you mean *"running sum"*? — Olivier Jacot-Descombes, Sep 19 '18 at 18:35
Many methods to calculate a running total can be found here: https://stackoverflow.com/questions/11310877/calculate-running-total-running-balance — C B, Sep 19 '18 at 18:44
Possible duplicate of [Calculate running total / running balance](https://stackoverflow.com/questions/11310877/calculate-running-total-running-balance) — C B, Sep 19 '18 at 18:50
Use the variant with `SUM(amt) OVER (ORDER BY TID ROWS UNBOUNDED PRECEDING)` from [this answer](https://stackoverflow.com/a/11313533/880990). — Olivier Jacot-Descombes, Sep 20 '18 at 09:31

SQL Server, SQL Query Assistance Required

0 Answers0

Linked