Sum Up the value accodring to the Department Structure.

  • 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.

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

    ----------
    Ashish

  • Hai

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

  • 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

  • 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

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

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