August 2, 2011 at 8:01 am
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...
August 2, 2011 at 8:24 am
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply