Sum Data In Hierarchy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Sum Data In Hierarchy

Author

Topic

impichit
Starting Member

2 Posts

Posted - 2010-07-11 : 13:36:52

I problem with Hierachy with my store procedure I need calculatate data but I don't know how to please advise. Thank

--Source
ALTER PROC [dbo].[ShowHierarchy_SubTract]
(
@Root varchar(10)
)
AS
BEGIN
DECLARE @Score Money
DECLARE @Sponser Varchar

SET NOCOUNT ON
DECLARE @MemDcode varchar(10), @MemFName varchar(30)

SET @MemFName = (SELECT MemFname FROM dbo._Member WHERE MemDcode = @Root)

SET @Score = 0
SET @Score = (SELECT MemTotal FROM dbo._Member WHERE MemDcode = @Root)

PRINT REPLICATE('-', @@NESTLEVEL * 4) + '> MemID : ' + @Root + ' Name : ' +@MemFName + ' Total : '+ CAST(@Score AS VARCHAR)

SET @MemDcode = (SELECT MIN(MemDcode) FROM _Member WHERE MemItDcode = @Root)

WHILE @MemDcode IS NOT NULL
BEGIN
EXEC ShowHierarchy_SubTract @MemDcode
SET @MemDcode = (SELECT MIN(MemDcode) FROM _Member WHERE MemItDcode = @Root AND MemDcode > @MemDcode)
END
END

--Execute
EXEC [dbo].[ShowHierarchy_subtract] 'MBA06863'

--OutPut
----> MemID : MBA06863 Name : ?????? Total : 1411.20
--------> MemID : MBA06865 Name : ????? Total : 0.00
--------> MemID : MBA06901 Name : ??????? Total : 0.00
------------> MemID : MBA06912 Name : ?????? Total : 494.40
----------------> MemID : MBA06919 Name : ??????? Total : 168.00
--------------------> MemID : MBA06920 Name : ?????? Total : 42.00
----------------> MemID : MBA06983 Name : ?????????? Total : 241.20
------------> MemID : MBA06922 Name : ??????? Total : 0.00
----------------> MemID : MBA06938 Name : ??????? Total : 0.00
----------------> MemID : MBA06994 Name : ?????? Total : 273.60
--------> MemID : MBA06972 Name : ????????????? Total : 0.00

--Need Output
1411.2 - 494.4 - 273.6 = 643.2
Please asvise.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-13 : 13:59:23

what makes those three rows related to each other? is there another field (datetime or whatever) that they have in common?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources