Binary Tree Counting Left and Right Node..

  • Hi all...

    i am new to SQL Server 2008.

    For Creating Table :

    CREATE TABLE binary_tree (

    aid bigint UNIQUE,

    name varchar(150) NOT NULL,

    parent_aid bigint NULL,

    position char(1) NULL,

    current_level int NULL,

    doj date NOT NULL,

    status INT NOT NULL DEFAULT '1')

    Inserting Values :

    This is Root node so parent_aid and position should be NULL

    INSERT INTO binary_tree (aid, name, current_level, doj) VALUES (1, 'name1', 0, '2011-07-13')

    Child node inserting values :

    INSERT INTO binary_tree (aid, name, parent_aid, position, current_level, doj) VALUES (2, 'name2', 1, 0, 1, '2011-07-31')

    INSERT INTO binary_tree (aid, name, parent_aid, position, current_level, doj) VALUES (3, 'name3', 1, 1, 1, '2011-07-31')

    INSERT INTO binary_tree (aid, name, parent_aid, position, current_level, doj) VALUES (4, 'name4', 2, 0, 2, '2011-08-01')

    INSERT INTO binary_tree (aid, name, parent_aid, position, current_level, doj) VALUES (5, 'name5', 2, 1, 2, '2011-08-02')

    INSERT INTO binary_tree (aid, name, parent_aid, position, current_level, doj) VALUES (6, 'name6', 3, 0, 2, '2011-08-01')

    INSERT INTO binary_tree (aid, name, parent_aid, position, current_level, doj) VALUES (7, 'name7', 3, 1, 2, '2011-08-02')

    Finally We got the following Tree Structure

    1

    (Date of Joining : 2011-07-13)

    2 3

    (doj:2011-07-31) (doj:2011-07-31)

    4 5 6 7

    (doj:2011-08-01)(doj:2011-08-01) (doj:2011-08-01)(doj:2011-08-02)

    I use the following code

    declare @nodeid int = 1

    ;with cte as (

    select

    aid, parent_aid, position,

    null lnode,

    null rnode

    from binary_tree where aid = @nodeid

    union all

    select

    t.aid, t.parent_aid, t.position,

    ISNULL(cte.lnode, CASE WHEN t.position = 0 THEN 1 ELSE 0 END) lnode,

    ISNULL(cte.rnode, CASE WHEN t.position = 1 THEN 1 ELSE 0 END) rnode

    from binary_tree t

    inner join cte

    on cte.aid = t.parent_aid

    )

    select

    @nodeid aid,

    SUM(lnode) LeftNodes,

    SUM(rnode) RightNodes

    from cte

    It counts left and right node very fine.

    No we get @nodeid=1 node left and right count is : 3:3 likewise all availble child nodes.

    But i want count node using Date of Joining [i.e, I want get the right and left node count in a particular day]

    so i am changed the above code like below...

    declare @nodeid int = 1

    ;with cte as (

    select

    aid, parent_aid, position,

    null lnode,

    null rnode

    from binary_tree where aid = @nodeid

    union all

    select

    t.aid, t.parent_aid, t.position,

    ISNULL(cte.lnode, CASE WHEN t.position = 0 THEN 1 ELSE 0 END) lnode,

    ISNULL(cte.rnode, CASE WHEN t.position = 1 THEN 1 ELSE 0 END) rnode

    from binary_tree t

    inner join cte

    on cte.aid = t.parent_aid AND doj = '2011-08-01'

    )

    select

    @nodeid aid,

    SUM(lnode) LeftNodes,

    SUM(rnode) RightNodes

    from cte

    it shows left and right count is null but insted of using this doj = '2011-07-31' it shows left and right node count 1:1....

    and also i want to get left and right child name list in a particular give node....

    please help me...

    thanks in advance...

  • Please don't cross post. It just wastes peoples time and fragments replies.

    No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic1152220-391-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 2 (of 2 total)

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