SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SUM Value Row by Row in SSMS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kond.mohan
Posting Yak Master

India
196 Posts

Posted - 05/25/2013 :  10:50:45  Show Profile  Reply with Quote
Hi
i have amount column based based on Monthyear.amount column is the sumvalue. based on amt field need YEARWISE FIELD IN THE Below criteria.how to do summation on row by row.

if anyone know help me

---monthwise -----year wise
amt monthyear yearewise
28.90 2013-01 28.90
25.78 2013-02 54.68
45.23 2013-03 99.21


visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/25/2013 :  12:02:42  Show Profile  Reply with Quote

;With CTE
AS
(
Your Current query with amount field
)

SELECT *
FROM CTE c
CROSS APPLY (SELECT SUM(amt) AS YrWise
             FROM CTE
             WHERE REPLACE(monthyear,'-','') * 1  < = REPLACE(c.monthyear,'-','')
             )c1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/25/2013 :  12:13:12  Show Profile  Reply with Quote
Here is another way:


DECLARE @AccTbl TABLE(amt MONEY, monthyear NVARCHAR(8))
INSERT INTO @AccTbl 
SELECT 28.90,  '2013-01' union all
SELECT 25.78, '2013-02' union all 
SELECT 45.23, '2013-03' union all
SELECT 28.90,  '2013-04' union all
SELECT 25.78, '2013-08' union all 
SELECT 45.23, '2013-12' union all
SELECT 28.90,  '2014-01' union all
SELECT 25.78, '2014-02' union all 
SELECT 45.23, '2014-03';


SELECT
	amt,
	monthyear,
	yearwise = amt + COALESCE(
	(
		SELECT SUM(amt)
			FROM @AccTbl AS S
			WHERE SUBSTRING(S.monthyear,1, 4) = SUBSTRING(O.monthyear,1, 4) AND 
			SUBSTRING(S.monthyear, 6, 2) < SUBSTRING(O.monthyear, 6, 2)), 0
	)
FROM @AccTbl AS O
ORDER BY SUBSTRING(monthyear,1, 4),
	   SUBSTRING(monthyear, 6, 2)


Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 05/30/2013 :  04:39:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Alternatively if you show these data in a front end application, you can do this there too

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000