Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Running total of records related to hierarchy

    Hi guys

    I'm mulling over the best way to do something and would like your input. Forgive me if this is a bit 101 - I haven't ever had to do this in SQL before!

    Fairly standard set up -
    Hierarchy table modelling the structure of an organisation.
    Related table associating members of staff to the hierarchy.

    I want to return all levels of the hierarchy and for each level I would like to know the total number of people in the level (so for a division it would be the sum of all people in the child teams).

    Parameters -
    • This table will be modelling many organisations' structures - I cannot guarentee anything like "there will never be more than n levels". As such - I would strongly prefer to have something that is iterative\ recursive.
    • I can change the schema to suit the method I use if necessary.
    • Database is not transactional - I am not concerned about updating speed.
    • SQL Server 2K5.
    I've tried CTE but it turns out you cannot use group by in CTEs (even in derived tables). I have not yet tried feeding it a view or similar.
    I have not tried nested sets, materialised paths, accumulator table - I thought I would see if there is something obvious before I start piddling around with those.

    Ta!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I got the CTE (syntactically) working with a view. But DUH! The running total, of course, needs to traverse the hierarchy from the bottom up. You work with these things from the top down. As such, I add the count of managers of divisions (i.e. 1) to the count of the team members rather than the other way round.

    I suspect I need to make use of temp tables and some loops.... How crude!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok then - with generous use of the words n00b, moron and "- FACT" please explain to me why this is a dumb idea (apart from RI - I would try to fudge something with triggers):

    Code:
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.elbat_yhcrareih')) BEGIN
        DROP TABLE dbo.elbat_yhcrareih
    END
    
    --It's a hierarchy table in reverse - get it?
    CREATE TABLE dbo.elbat_yhcrareih
        (
            mycode            VARCHAR(10)    NOT NULL
            , mychildcode    VARCHAR(10)    NULL
        )
    GO
    
    INSERT INTO dbo.elbat_yhcrareih (mycode, mychildcode)
    SELECT    '1'    , NULL
    UNION ALL
    SELECT    '2'    , NULL
    UNION ALL
    SELECT    '3'    , NULL
    UNION ALL
    SELECT    '4'    , '1'
    UNION ALL
    SELECT    '4'    , '2'
    UNION ALL
    SELECT    '4'    , '3'
    
    SELECT    *
    FROM    dbo.elbat_yhcrareih
    Last edited by pootle flump; 04-15-08 at 06:11.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2007
    Posts
    183

    Here is a starter

    Code:
    DECLARE	@Sample TABLE (myCode VARCHAR(10), myChildCode VARCHAR(10), myValue INT)
    
    INSERT	@Sample
    SELECT	'1', NULL, 11 UNION ALL
    SELECT	'2', NULL, 13 UNION ALL
    SELECT	'3', NULL, 15 UNION ALL
    SELECT	'4', '1', 9 UNION ALL
    SELECT	'4', '2', 1 UNION ALL
    SELECT	'4', '3', 7
    
    ;WITH Yak (myCode, myChildCode, myValue, rtValue)
    AS (
    	SELECT	myCode,
    		myChildCode,
    		myValue,
    		myValue
    	FROM	@Sample
    	WHERE	myChildCode IS NULL
    
    	UNION ALL
    
    	SELECT		s.myCode,
    			s.myChildCode,
    			s.myValue,
    			y.rtValue + s.myValue
    	FROM		@Sample AS s
    	INNER JOIN	Yak AS y ON y.myCode = s.myChildCode
    )
    
    SELECT	*
    FROM	Yak
    /*
    SELECT		myCode,
    		SUM(rtValue) AS rtValue
    FROM		Yak
    GROUP BY	myCode
    */
    Last edited by Peso; 04-15-08 at 07:42.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks Peter

    Edited the data to better represent the problem (mycode 4 would have the same count for all three lines). Changed the SQL to get the final output. I don't think it can be optimised much (happy to be corrected):

    Code:
    DECLARE    @Sample TABLE (myCode VARCHAR(10), myChildCode VARCHAR(10), myValue INT)
    
    INSERT    @Sample
    SELECT    '1', NULL, 10 UNION ALL
    SELECT    '2', NULL, 130 UNION ALL
    SELECT    '3', NULL, 93 UNION ALL
    SELECT    '4', '1', 1 UNION ALL
    SELECT    '4', '2', 1 UNION ALL
    SELECT    '4', '3', 1
    
    ;WITH Yak
    AS (
        SELECT    myCode,
            myChildCode,
            myValue
        FROM    @Sample
        WHERE    myChildCode IS NULL
    
        UNION ALL
    
        SELECT        s.myCode,
                s.myChildCode,
                y.myValue + s.myValue
        FROM        @Sample AS s
        INNER JOIN    Yak AS y ON y.myCode = s.myChildCode
    )
    SELECT    Yak.mycode
            , headcount    = SUM(myvalue) - ((COUNT(*) - 1) * COALESCE(node_headcount, 0))
    FROM    Yak
    LEFT OUTER JOIN 
            (SELECT    myCode
                    , node_headcount    = MAX(myValue)
            FROM    @sample
            GROUP BY myCode
            HAVING    COUNT(*) > 1) AS node_count
    ON    node_count.myCode    = Yak.mycode
    GROUP BY Yak.mycode
            , node_headcount
    ORDER BY Yak.mycode
    So - this structure & code work for this one requirement. Is this a deadend - am I likely to just create myself other headaches down the road by recording children instead of a single parent?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Wow - Google doesn't take long to indexify:
    http://www.google.co.uk/search?hl=en...G=Search&meta=
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I can find stuff all about traversing adjacency tables in reverse. I can find stuff all about storing hierarchical data in reverse. Apart from Peter's SQL I've had stuff all assistance from my peers.

    I don't feel comfortable storing the hierarchy in reverse. I suspect I will run up against problems I have not yet considered. I don't really have the appetite to look at other models in this context.

    I am going to go for the standard adjacency model and use a view to construct the reverse-hierarchy for the purposes of this one requirement. This at least keeps the schema and DRI clean and transparent.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I really wish I understood this topic better; because it sounds just like a problem I tried to overcome recently...

    My gut reaction was to remodel what I had (which was a defined 4 level structure) into a detached adjacency model like this; but couldn't persuade the powers that be that this was a good idea.

    *shrug*
    Sorry I can't be very useful on this one, but it's certainly something I [B
    ]need[/B] to learn.
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In case you have more time on your hands than me - I understand that nested sets and materialised paths are equally happy working their way down as well as up the hierarchy.

    BTW - detatched adjacency returns no hits from google. Do you mean the sort of adjacency where only the explicit relationships are stored, not the implicit ones?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    By detached I mean it is a separate table in the schema, for example:

    instead of storing the manager_id in the employees table, you store this information in a detached fact table.

    I make no sense, do I?
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why would you do dat den? I have done that before - but only because we had a guy who was "dual-managed".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    On my wanderings I came across this; which probably won't benefit you Poots; but it could be useful for future readers

    http://www.setfocus.com/TechnicalArt...05-tsql-3.aspx

    I'm so rubbish with CTE's and Recursive queries
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •