Running Total Using new MS SQL Function

need sample data
and result that you look for

You can use a trigger what is adding these entries?

Usually a balance is kept in another table
You are leaving all the information out.
DB structure, and .,.
You can have a transaction table and triggers that update a view ......or a table that maintains the balance.
Begin - debit +credit is the new balance..

Select item, Name, Begin, Debit, Credit, (Begin + Debit - Credit) as Balance
From (Select item, Name, Begin, Debit, Credit From TableName)D

Select allOpen in new window

Here I have used a derived table subquery. see part 3 at Subqueries at a glance

you dont need subquery

Select item, Name, Begin, Debit, Credit, (Begin + Debit - Credit) as Balance
From TableName

Select allOpen in new window

Without details the reliance that begin, debit, credit are accurate and ....

@KAHRAMAN,

Both (ID: 42101998 and ID: 42101984) will work. The advantage of using a derived table is in case of some calculation, for example applying a percentage), the derived table will work but the other one would fail. This is because SQL Server unlike MS Access doesn't allow all at once processing.


To illustrate:  The following SQL works in MS Access but not in SQL Server.

Select CheckAmount, CashAmount,  (CheckAmount + CashAmount) As Sum, (Sum * 0.10) As TenPercent From Table1  

SQL Server solution will be:

Select CheckAmount, CashAmount, Sum * 0.10 As TenPercent
From (Select CheckAmount, CashAmount,  (CheckAmount + CashAmount) As Sum From Table1) D

Hi All,

item    Name          Begin    Debit   Credit   Balance
A         Coca Cola    100       20         30         90
A         Coca Cola    90         10         40         60
B ...etc

Thank you.

Is your Begin same as previous Balance?
What information you have in your table and what do you want to derive? Just Balance or both Balance and Begin?
If you want to derive Begin for next record, do you have the starting Begin?

Hi Sharath,

There are just transactions. The begining balance also a transaction.

TrsNo   Date   item    Debit    Credit

Thank you.

Running Total Using new MS SQL Function

I think you are after a combination of 2 commands: SUM() an aggregate function, and OVER() which is a "clause":
as in the following example:

declare @Table1 table
    ([item] varchar(1), [ItemDate] datetime, [Begin] int, [Debit] int, [Credit] int)
;
    
INSERT INTO @Table1
    ([item], [ItemDate], [Begin], [Debit], [Credit])
VALUES
    ('A', '2017-01-01 00:00:00', 100, 20, 30),
    ('A', '2017-01-02 00:00:00', 90, 10, 40)
;


select
     *
    , [begin] + sum(debit-credit) over(partition by item order by itemdate) run_total
from @table1

Select allOpen in new window

resutlt:

+------+---------------------+-------+-------+--------+-----------+
| item |      ItemDate       | Begin | Debit | Credit | run_total |
+------+---------------------+-------+-------+--------+-----------+
| A    | 01.01.2017 00:00:00 |   100 |    20 |     30 |        90 |
| A    | 02.01.2017 00:00:00 |    90 |    10 |     40 |        50 |
+------+---------------------+-------+-------+--------+-----------+

Select allOpen in new window

Hi PortletPaul,

The transaction has no begin column.

TrsNo   Date   item    Debit    Credit

Thank you.

The question asked for the syntax - which I  provided.

Try applying it to your situation. If you need help after you try it, let me know. Don't forget there is the documentation - i provided a link earlier.

perhaps this will help?

select
     *
    , sum(credit) over(partition by item order by itemdate) 
    - sum(debit) over(partition by item order by itemdate) 
    run_total
from @table1

Select allOpen in new window

Hi Megan Brooks,

Yes, it is. But almost.

How could I get column Begin Balance that from previous row of Balance column (Running Total) ?

item    Name          Begin    Debit   Credit   Balance
A         Coca Cola    100       20         30         90
A         Coca Cola    90         10         40         60
B ...etc

Thank you.

You want all transactions reflected?
You should clearly state what it is you are looking for given a sample data.
It seems you want a running view of all transactions that reflect
item, name, begining balance, debit/credit, balance

though it makes no sense a transaction would either be a debit, or a credit, it would rarely be both.
A,coca cola, 100,10,0,110
A,cocal cola,110,0,10,100
a,coca cola,100,0,10,90

the begin, debit, credit, balance

is the credit/debit apply to the Name to whom this is owed?

ok try this

with t as (
    select '1001' TrxNo, '2017-01-01 00:00:00' TrxDate, 'Opening' Item, 0 Debit, 100 Credit
    union select '1002', '2017-01-02 00:00:00', 'A', 20, 30
    union select '1003', '2017-01-03 00:00:00', 'B', 10, 40
    union select '1003', '2017-01-03 00:00:00', 'B', 60, 15
)
select TrxNo, TrxDate, Item, 
IsNull(sum(credit - debit) over (order by TrxNo ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) as Opening,
Debit, Credit,
sum(credit - debit) over (order by TrxNo rows UNBOUNDED PRECEDING) as cumulative_sales
from t

TrxNo	TrxDate	Item	Opening	Debit	Credit	cumulative_sales
1001	2017-01-01 00:00:00	Opening	0	0	100	100
1002	2017-01-02 00:00:00	A	100	20	30	110
1003	2017-01-03 00:00:00	B	110	10	40	140
1003	2017-01-03 00:00:00	B	140	60	15	95

Select allOpen in new window

The LAG window function returns results from a previous row. (I'm out of the house/office most of this weekend . Perhaps someone else can provide a better response.)

Hi Huseyin,

This is also wrong/

1002      2017-01-02 00:00:00      A      100      20      30      110
1003      2017-01-03 00:00:00      B      110      10      40      140

Item B begin balance 110 is from item A.
Every item should have begin balance for their on stock.

Thank you.

Hi All,

Thank you very much for your help.