August 1, 2011 at 5:10 pm
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-02) (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 1, 2011 at 6:52 pm
If you really want help, post the data in a readily consumable format. See the first link in my signature line below for how to do that correctly.
As a side bar... we go for months and sometimes years without a single question about binary trees. I've seen 3 in less than the last week. I'm starting to think it's a homework problem somewhere. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 1, 2011 at 7:22 pm
Lol final weeks are going on for summer classes in many schools 🙂
August 2, 2011 at 5:09 am
Jeff Moden (8/1/2011)
If you really want help, post the data in a readily consumable format. See the first link in my signature line below for how to do that correctly.As a side bar... we go for months and sometimes years without a single question about binary trees. I've seen 3 in less than the last week. I'm starting to think it's a homework problem somewhere. 😉
Laura_SqlNovice (8/1/2011)
Lol final weeks are going on for summer classes in many schools 🙂
Friends i am new to SQL Server and this forum also I want any solution. please any body help me...
August 2, 2011 at 7:05 am
Same message, talktosivas... lots of people want and need help and lots of good people on the forums want to test their answer against some data before posting their coded answer. As a result, many don't have or won't take the time to convert "plain text" data offered in a post to something they can load into a table for testing.
Please see the first link in my signature line below for how to provide "readily consumable" data to get better answers quicker.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2011 at 7:21 am
Thank you for you reply.... i will post my details in that format... really sorry ya...
August 2, 2011 at 7:58 am
please see my edited topic... Thank you and sorry for inconvenience for all....
August 2, 2011 at 11:06 am
declare @nodeid int = 1
declare @doj date = '2011-08-01'
;with cte as (
select
aid, parent_aid, position, doj,
null lnode,
null rnode
from binary_tree where aid = @nodeid
union all
select
t.aid, t.parent_aid, t.position, t.doj,
CASE WHEN t.position = 0 AND t.doj = @doj THEN 1 ELSE 0 END lnode,
CASE WHEN t.position = 1 AND t.doj = @doj 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
order by aid
August 2, 2011 at 1:57 pm
Peter Brinkhaus (8/2/2011)
declare @nodeid int = 1
declare @doj date = '2011-08-01'
;with cte as (
select
aid, parent_aid, position, doj,
null lnode,
null rnode
from binary_tree where aid = @nodeid
union all
select
t.aid, t.parent_aid, t.position, t.doj,
CASE WHEN t.position = 0 AND t.doj = @doj THEN 1 ELSE 0 END lnode,
CASE WHEN t.position = 1 AND t.doj = @doj 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
order by aid
Please use nodeid int = 2 its not working fine...
August 2, 2011 at 2:08 pm
What's wrong with it? What do you expect?
August 3, 2011 at 10:01 am
its work for root node id only...
int aid=1
but using aid=2 na it not working friend...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply