July 28, 2010 at 4:12 am
CREATE TABLE [dbo].[tbl_tree](
[node_id] [varchar](50) NOT NULL,
[parent_id] [varchar](50) NULL,
[node] [bit] NULL(False means Leftside and True means Rightside)
[JoinDate] Datetime
node_id parent_id node JoinDate
a Null False 17/7/2010
b a False 17/7/2010
c a True 17/7/2010
d b False 24/7/2010
e b True 24/7/2010
;with cte as (
select
node_id, parentid, node,
null lnode,
null rnode
from tbl_tree where node_id = 'a'
union all
select
t.node_id, t.parentid, t.node,
ISNULL(cte.lnode, CASE WHEN t.node = 0 THEN 1 ELSE 0 END) lnode,
ISNULL(cte.rnode, CASE WHEN t.node = 1 THEN 1 ELSE 0 END) rnode
from tbl_tree t
inner join cte
on cte.node_id = t.parentid AND JoinDate = '24/7/2010'
)
select
SUM(lnode) LeftNodes,
SUM(rnode) RightNodes
from cte
whats a problm in this solution..
i want to output is : LeftNodes-2 and RightNodes-0
thank u
August 1, 2011 at 5:11 pm
i am also the same problem...
please help me...
Hi all...
i am new to SQL Server 2008.
please look this 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
the above code is used for count the left and right node of binary tree's id=1...
Its working fine. But i want count particular day or each day right and left calculation.
i am using like this :
declare @nodeid int = 1
;with cte as (
select
aid, parent_aid, position,
null lnode,
null rnode
from bi_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 bi_tree t
inner join cte
on cte.aid = t.parent_aid AND doj = '2011-08-02'
)
select
@nodeid aid,
SUM(lnode) LeftNodes,
SUM(rnode) RightNodes
from cte
its also working good... what is the problem arise na...
if the the tree table contains the following dates
aidparent_aidpositiondoj
1 NULLNULL 2011-07-13
2102011-08-01
3112011-08-01
4302011-08-01
5312011-08-01
8502011-08-01
9512011-08-01
11202011-08-01
12212011-08-01
131102011-08-01
151402011-08-01
232202011-08-02
242302011-08-02
252312011-08-02
262502011-08-02
281202011-08-02
291002011-08-02
301012011-08-02
313002011-08-02
323102011-08-02
333202011-08-02
393712011-08-02
6402011-08-01
10902011-08-01
141112011-08-01
161502011-08-01
171602011-08-01
181612011-08-01
191802011-08-02
201302011-08-02
211312011-08-02
272602011-08-02
343212011-08-02
36602011-08-02
373602011-08-02
383702011-08-02
403902011-08-02
7412011-08-01
222002011-08-02
353402011-08-02
now i want count particular date's left and right node count how its possible? i am using that above code did not find the node count. Its shows Null for both left and right count.
What is the problem in that code?
Please help me thanks a lot in advance...
Viewing 2 posts - 61 through 62 (of 62 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy