Sum Up the value accodring to the Department Structure.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Sum Up the value accodring to the Department...

Sum Up the value accodring to the Department Structure.

Rate Topic

Display Mode

Topic Options

Author

Message

mohanaraj

Posted Thursday, September 16, 2010 5:07 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, September 30, 2010 2:15 AM
Points: 30, Visits: 36

Hai,

I want to sum up the child department values and display in the parent record

Below is the Table.

DepartmentId ParentId DeptName Incoming
1 ------------0------ PhnDeprt ------14.00
3 ------------1-------Facilities ------01.00
2 ------------0-------Calse ---------- 5.00
4 ------------2-------CalFacitity ------1.00
5 ----------- 4 -------CalFF -----------2.00

I want the result as

DepartmentId ParentId DeptName Incoming
1 ------------0------ PhnDeprt ------15.00
3 ------------1-------Facilities ------01.00
2 ------------0-------Calse ---------- 8.00
4 ------------2-------CalFacitity ------3.00
5 ----------- 4 -------CalFF -----------2.00

Here 'PhnDeprt' department have child 'Facilities' so in the result 1.00 + 14.00 = 15.00 this result will be displayed in the Parent record i.e. in the 'PhnDeprt''s Incoming column

Please advise and help me

Thanks in advance.

Post #987109

crazy4sql

Posted Thursday, September 16, 2010 5:48 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:10 PM
Points: 876, Visits: 3,731

is facilities always 1? What type of field is storing value '1' or '15'? Is it int or varchar?

----------
Ashish

Post #987143

mohanaraj

Posted Thursday, September 16, 2010 5:55 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, September 30, 2010 2:15 AM
Points: 30, Visits: 36

Hai

Incoming Is the Decimal type field. This field have any type of values in decimal.

Post #987147

sinureddi

Posted Monday, September 27, 2010 3:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 04, 2012 3:09 AM
Points: 7, Visits: 81

Hi,

Try the following code:

with Incoming as(
select DepartmentId, ParentId, Incoming as Incoming from #Department
where ParentId in (select DepartmentId from #department)
Union All
select d.DepartmentId, i.ParentId, d.Incoming as Incoming
from #Department d Inner Join Incoming i
ON
i.DepartmentId = d.ParentId
)

select d.DepartmentId, d.ParentId, (d.incoming+isnull(i.incoming,0)) as incoming
from
#Department d Left Outer Join (Select ParentId, Sum(Incoming) as incoming from Incoming group by ParentId) i
ON d.DepartmentId = i.ParentId

Srinivas Reddy.S

Post #993530

ChrisM@Work

Posted Monday, September 27, 2010 5:12 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 5,618, Visits: 10,987

Here's a simple solution which assumes only three levels in the hierarchy, as in your sample data. Note how the sample data has been set up using a script:

DROP TABLE #Sample
CREATE TABLE #Sample (DepartmentId INT, ParentId INT, DeptName VARCHAR(20), Incoming DECIMAL(5,2))
INSERT INTO #Sample (DepartmentId, ParentId, DeptName, Incoming)
SELECT 1, 0, 'PhnDeprt', 14.00 UNION ALL
SELECT 3, 1, 'Facilities', 01.00 UNION ALL
SELECT 2, 0, 'Calse', 5.00 UNION ALL 
SELECT 4, 2, 'CalFacitity', 1.00 UNION ALL
SELECT 5, 4, 'CalFF', 2.00

SELECT d.DepartmentId, d.ParentId, d.DeptName, 
	Level0Incoming = d.Incoming, 
	Level1Incoming = c1.Incoming, 
	Level2Incoming = c2.Incoming 
FROM #Sample d 
LEFT JOIN #Sample c1 ON c1.ParentId = d.DepartmentId 
LEFT JOIN #Sample c2 ON c2.ParentId = c1.DepartmentId

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #993560

« Prev Topic | Next Topic »

Permissions