Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sum Up the value accodring to the Department Structure. Expand / Collapse
Author
Message
Posted Thursday, September 16, 2010 5:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, September 16, 2010 5:48 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:10 AM
Points: 880, Visits: 4,092
is facilities always 1? What type of field is storing value '1' or '15'? Is it int or varchar?

----------
Ashish
Post #987143
Posted Thursday, September 16, 2010 5:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Monday, September 27, 2010 3:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 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
Posted Monday, September 27, 2010 5:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:14 AM
Points: 7,176, Visits: 13,626
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 »

Add to briefcase

Permissions Expand / Collapse