Click here to Skip to main content
14,303,339 members
Rate this:
Please Sign up or sign in to vote.
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
Rate this:
Please Sign up or sign in to vote.

Solution 1

Check this article & customize it
Calculating simple running totals in SQL Server[^]
   
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!
Rate this:
Please Sign up or sign in to vote.

Solution 3

Have a look at example:
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
   
Comments
Amir Mahfoozi 16-Dec-13 7:28am
   
+5
Maciej Los 16-Dec-13 7:59am
   
Thank you, Amir ;)
thatraja 16-Dec-13 9:19am
   
You have plenty of time, 5!
Maciej Los 16-Dec-13 9:21am
   
Thank you ;)
Rate this:
Please Sign up or sign in to vote.

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
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100