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 61 (of 61 total)
You must be logged in to reply to this topic. Login to reply