Sum Up the value accodring to the Department Structure.

Question

Post reply

Sum Up the value accodring to the Department Structure.

mohanaraj

SSC-Addicted

Points: 406
More actions
September 16, 2010 at 5:07 am

#387799

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

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

----------
Ashish

mohanaraj SSC-Addicted Points: 406 More actions · Answer 2

Hai

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

sinureddi SSC Journeyman Points: 97 More actions · Answer 3

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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