Running total/balance query in sql server 2005 with two column

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

I want the output like below, i am having this four column, please help me to generate fifth column with sql query.

31-Mar-13	Product-A	50000	0	50000
2-Apr-13	Product-A	0	2000	48000
4-Apr-13	Product-A	0	3000	45000
6-Apr-13	Product-A	0	2500	42500
9-Apr-13	Product-A	0	2500	40000
11-Apr-13	Product-A	0	3000	37000
15-Apr-13	Product-A	0	3000	34000
16-Apr-13	Product-A	0	2000	32000
18-Apr-13	Product-A	0	1000	31000
20-Apr-13	Product-A	0	2000	29000

Posted 15-Dec-13 22:23pm

Nikhil Bhivgade

Add a Solution

3 solutions

Solution 1

Check this article & customize it
Calculating simple running totals in SQL Server[^]

Posted 15-Dec-13 22:28pm

thatraja

Comments

Nikhil Bhivgade 16-Dec-13 4:34am

i am having two columns that is of one column i need balance qty

thatraja 16-Dec-13 5:18am

I said customize it. You need to subtract the 3rd column value from the final result, that's it. Try it yourself, that's easy

Maciej Los 16-Dec-13 6:15am

Very good, general advice!
A10!

Solution 2

Hello Try this query.

select s.col1 as Date,s.col2 as Product,s.col3 as Cr,s.col4 as Db,(select sum(col3)-sum(col4) from this_table t where t.Col1 < s.col1) from this_table s

Posted 16-Dec-13 0:13am

BK 4 code

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-12-16T00:43:00

Have a look at example:

SQL

DECLARE @tmp TABLE(ID INT IDENTITY(1,1), PaymentIn INT, PaymentOut INT)

INSERT INTO @tmp (PaymentIn, PaymentOut)
VALUES(50000, 0), (0, 2000),
    (0, 3000), (0, 2500),
    (0, 2500), (0, 3000),
    (0, 3000), (0, 2000),
    (0, 1000), (0, 2000)


SELECT t1.ID, t1.PaymentIn, t1.PaymentOut, (SELECT SUM(t2.Balance)
    FROM
    (
        SELECT ID, PaymentIn, PaymentOut, PaymentIn AS Balance
        FROM @tmp
        UNION ALL
        SELECT ID, PaymentIn, PaymentOut, PaymentOut *-1 AS Balance
        FROM @tmp
    ) AS t2 WHERE t2.ID<=t1.ID) AS Total
FROM @tmp AS t1

Result:

ID      Pa.In   Pa.Out  Total
1	50000	0	50000
2	0	2000	48000
3	0	3000	45000
4	0	2500	42500
5	0	2500	40000
6	0	3000	37000
7	0	3000	34000
8	0	2000	32000
9	0	1000	31000
10	0	2000	29000