September 27, 2011 at 3:01 am
I have a table with field name id, reff, direction, pv. here id is chiled id, reff is parent id, direction is left or right and pv contained 0 or 1.
table is
id---------------reff---------------direction------pv
201--------------Root---------------Root------------1
601--------------201----------------L---------------0
1101-------------601----------------L---------------1
1201-------------1101---------------R---------------1
501--------------201----------------R---------------1
1001-------------501----------------L---------------1
1901-------------1001---------------L---------------1
2001-------------1901---------------L---------------1
2301-------------2001---------------L---------------1
2401-------------2301---------------L---------------0
2501-------------2401---------------L---------------1
2601-------------2401---------------R---------------1
2201-------------2001---------------R---------------1
1801-------------1001---------------R---------------1
901--------------501----------------R---------------1
1601-------------901----------------L---------------1
1701-------------901----------------R---------------1
I make a query below
declare @nodeid int = 201;
with cte as (
Select id, reff, direction, pv, lnode= null, rnode = null
From tree
Where id = @nodeid
Union All
Select t.id, t.reff, isnull(cte.direction,t.direction), t.pv,
lnode = CASE WHEN isnull(cte.direction,t.direction) = 'L' and t.pv = 1 THEN 1 ELSE 0 END,
rnode = CASE WHEN isnull(cte.direction,t.direction) = 'R' and t.pv = 1 THEN 1 ELSE 0 END
From tree t
Inner Join cte
On t.reff = cte.id
) select @nodeid id, SUM(lnode) LeftNodes, SUM(rnode) RightNodes from cte
if I pass root ID 201 then
here total left node = 3
total Right Node = 13
here node 601 (Left) PV is 0 and
Node 2401 (Right) PV is 0
I want count left and right node of 201 where (chiled node pv =1)
Final output will be
total left node = 2
total right node = 12
Plz help me
September 27, 2011 at 2:19 pm
I see you are new around here. Welcome. We usually like to see some ddl and sample data in a consumable format. Something to make things easy for us to help you. Since this is your first post I went ahead and made some for you so you can see the format of what works best. You can also read the link in my signature about best practices for posting questions.
create table #Tree
(
MyId int,
ref int,
Direction char(1),
pv bit
)
insert #Tree (MyID, ref, Direction, pv)
values
('201', null, null, '1'),
('601', '201', 'L', '0'),
('1101', '601', 'L', '1'),
('1201', '1101', 'R', '1'),
('501', '201', 'R', '1'),
('1001', '501', 'L', '1'),
('1901', '1001', 'L', '1'),
('2001', '1901', 'L', '1'),
('2301', '2001', 'L', '1'),
('2401', '2301', 'L', '0'),
('2501', '2401', 'L', '1'),
('2601', '2401', 'R', '1'),
('2201', '2001', 'R', '1'),
('1801', '1001', 'R', '1'),
('901', '501', 'R', '1'),
('1601', '901', 'L', '1'),
('1701', '901', 'R', '1')
declare @nodeid int = 201;
with cte as (
Select Myid, ref, direction, pv, lnode= null, rnode = null
From #Tree
Where Myid = @nodeid
Union All
Select t.Myid, t.ref, isnull(cte.direction,t.direction), t.pv,
lnode = CASE WHEN isnull(cte.direction, t.direction) = 'L' and t.pv = 1 THEN 1 ELSE 0 END,
rnode = CASE WHEN isnull(cte.direction, t.direction) = 'R' and t.pv = 1 THEN 1 ELSE 0 END
From #Tree t
Inner Join cte
On t.ref = cte.Myid
)
select @nodeid as id, sum(lnode) as LeftNodes, sum(rnode) as RightNodes from cte
I used a temp table so others don't need a permanent table and you can test this without disrupting your real data.
When I run the above code I get EXACTLY the output you expressed you are looking for. If the output is incorrect, what do you want for the output?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 27, 2011 at 11:12 pm
its query produce left node = 0 and right node = 15, while left node will be 2 and rightnode will be 13
September 27, 2011 at 11:20 pm
dileep41011 (9/27/2011)
its query produce left node = 0 and right node = 15, while left node will be 2 and rightnode will be 13
The fix for that is quite simple. You simply need to add a where pv=1 to the final select.
This seems like an overly simplified Compensation Plan. You may want to seek out a consultant for help with the Actual count of PV, calculation of compensation and processing of your tree.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 27, 2011 at 11:29 pm
plz help me
September 27, 2011 at 11:58 pm
dileep41011 (9/27/2011)
plz help me
See my last reply where I provided the answer (the change needed to make Seans query work for you).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2011 at 12:09 am
what type changes required, plz suggest me, I am fresher
September 28, 2011 at 12:15 am
dileep41011 (9/28/2011)
what type changes required, plz suggest me, I am fresher
As I said - I already noted it once. Maybe this is another good reason to hire a consultant. MLMs are very particular about their compensation plans and this is not something to cut your teeth on.
You need to add a
where pv=1
to the code that Sean posted.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2011 at 3:01 am
Agar solution nahi de skte to kyun bolte ho solution dene ka, sb bakwas he tumhara solution, Left nodes 0 and right nodes 4 count kr rha he, quit here.
September 28, 2011 at 7:08 am
All I did was added some ddl and data in a consumable format and ran the query exactly as the OP posted it and it returned the exact results they were looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2011 at 10:16 am
Sean Lange (9/28/2011)
All I did was added some ddl and data in a consumable format and ran the query exactly as the OP posted it and it returned the exact results they were looking for.
I had to add the where pv=1 to your query but then it returned the results. Nonetheless - it should be good to go at this point.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply