Click here to Skip to main content
15,920,828 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

Check this article & customize it
Calculating simple running totals in SQL Server[^]
 
Share this answer
 
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!
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
 
Share this answer
 
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 ;)
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
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900