Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi guyz,

SQL
Balance          Month
-1308998.02	1
    -990000	2
 1178835.01	3
 -188835.01	4
          0	5
     794.99	6
  160589.98	7
    -159000	8
     1405.6	9
      13200	10
 1721328.02	11
  436984.49	12


I want to sum the balance with respective of their months. like month 1 and 2 after summing will be 2298998.02 and will be displayed against month 2. same for all
SQL
Balance       ance(after summing)        Month
-1308998.02	-1308998.02	1
    -990000	-2298998.02	2
 1178835.01	 3477833.03	3
 -188835.01	-3666668.04	4
          0  	 3666668.04	5
     794.99	 3667463.03	6
  160589.98 	 3828053.01	7
    -159000	-3987053.01	8
     1405.6	-3985647.41	9
      13200	 3998847.41	10
 1721328.02	 5720175.43	11
  436984.49	 6157159.92	12
Posted
Updated 9-Dec-13 21:54pm
v2

Don't use a cursor.
In SQL 2012, use windowing functions. See an example here: http://sqlmag.com/sql-server-2012/how-use-microsoft-sql-server-2012s-window-functions-part-1[^]
In earlier versions, use a CTE (Common Table Expression) and a subquery.

Hope this helps,
Pablo.
 
Share this answer
 
Comments
xibit89 10-Dec-13 4:34am    
the link u provided is for simple summing, my query for getting the above data is something like as follow,

SELECT
sum(ISNULL(F1.Debit,0))-sum(ISNULL(F1.Credit,0)) AS Balance,
month(f2.CreateDate) as mon , f1.ShortName

FROM
JDT1 AS F1
LEFT OUTER JOIN OJDT AS F2 ON F1.TransId = F2.TransId

WHERE
F1.RefDate BETWEEN '2009-01-01' AND '2012-12-31' and f1.ShortName = '109085'
group by f1.ShortName ,Month (f2.CreateDate)
order by f1.ShortName ,Month (f2.CreateDate)
Pablo Aliskevicius 10-Dec-13 6:02am    
Look for this in that page:

empid ordermonth qty runqty
----- ----------------------- ---- -------
1 2006-07-01 00:00:00.000 121 121
1 2006-08-01 00:00:00.000 247 368
1 2006-09-01 00:00:00.000 255 623
1 2006-10-01 00:00:00.000 143 766
1 2006-11-01 00:00:00.000 318 1084
...
2 2006-07-01 00:00:00.000 50 50
2 2006-08-01 00:00:00.000 94 144
2 2006-09-01 00:00:00.000 137 281
2 2006-10-01 00:00:00.000 248 529
2 2006-11-01 00:00:00.000 237 766

Column 'runqty' seems to be exactly what you want.
What you are looking for is "Running Total" and CP has a nice article on the subject Calculating simple running totals in SQL Server[^]

for more info you can do a google Search[^].
 
Share this answer
 
Comments
xibit89 10-Dec-13 4:34am    
the link u provided is for simple summing, my query for getting the above data is something like as follow,

SELECT
sum(ISNULL(F1.Debit,0))-sum(ISNULL(F1.Credit,0)) AS Balance,
month(f2.CreateDate) as mon , f1.ShortName

FROM
JDT1 AS F1
LEFT OUTER JOIN OJDT AS F2 ON F1.TransId = F2.TransId

WHERE
F1.RefDate BETWEEN '2009-01-01' AND '2012-12-31' and f1.ShortName = '109085'
group by f1.ShortName ,Month (f2.CreateDate)
order by f1.ShortName ,Month (f2.CreateDate)
Varsha Ramnani 12-Dec-13 2:15am    
Did you read the CP article it is exactly what you are looking for... it does running total.
you will need to use your query as subquery to the main query that does running total.
Also look at the other links. you will get the idea how to change your query to accomplish what you are looking for.
This should solve your problem but I don't know why I got different result ! Had you computed them manually ?

SQL
declare @tbl1 as table(
balance decimal(18,2),
month int)
insert into @tbl1 values (-1308998.02,  1)
insert into @tbl1 values (    -990000,  2)
insert into @tbl1 values ( 1178835.01,  3)
insert into @tbl1 values ( -188835.01,  4)
insert into @tbl1 values (          0,  5)
insert into @tbl1 values (     794.99,  6)
insert into @tbl1 values (  160589.98,  7)
insert into @tbl1 values (    -159000,  8)
insert into @tbl1 values (     1405.6,  9)
insert into @tbl1 values (      13200,  10)
insert into @tbl1 values ( 1721328.02,  11)
insert into @tbl1 values (  436984.49,  12)

select balance, month, (select sum(balance) from @tbl1 where month <= t.month ) [after sum] from @tbl1 t

Good Luck
 
Share this answer
 
Comments
xibit89 10-Dec-13 5:03am    
yep have computed manually!

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