help with celkos tree model

hello i have implemented joe celko's model to store heirarchy and it
works well and nice
i have a question about SQL

this is the actual table

member side left right
-------------
nancy L 1 36
andrew L 4 21
steven R 5 12
ina L 6 7
david R 10 11
margaret L 13 20
ann R 14 15
laura L 18 19
janet R 24 35
michael L 25 30
dan R 26 27
ron L 28 29
robert R 33 34

the Side column is to tell its left, or right. this is a binary
heirarcy.
i have this problem i have to solve, im still banging my head. If
given the member

'Nancy' , i need to find left-most(Laura) and right-most(Robert)
'Janet' = left most is ron, right most is robert
'Andrew = left most is laura, right most is David

Hope u get my plan. could u help me with the sql ?On 24 Oct 2004 23:04:40 -0700, Nick Chan wrote:

>hello i have implemented joe celko's model to store heirarchy and it
>works well and nice
(snip)
>the Side column is to tell its left, or right. this is a binary
>heirarcy.

Hi Nick,

You seem to have a slight misunderstanding about Joe Celko's nested set
model. A bit of googling got me this quote (from a message in another
newsgroup, written by Joe Celko himself):

"The nested set model has an implied ordering of siblings which
the adjacency list model does not."

From the context, it was clear that Joe meant that for each pair of
siblings, the one with the lower lft and rgt values should be considered
to be to the left of the one with the higher lft and rgt values.

I've drawn a picture of your hierarchy with the "left" descendant always
on the left side and the "right" descendant always on the right side; then
I re-assigned the lft and rgt numbers according to Joe Celko's nested set
model. The result looks like this:

member lft rgt
--------
nancy 1 26
andrew 2 15
margaret 3 8
laura 4 5
ann 6 7
steven 9 14
ina 10 11
david 12 13
janet 16 25
michael 17 22
ron 18 19
dan 20 21
robert 23 24

>i have this problem i have to solve, im still banging my head. If
>given the member
>'Nancy' , i need to find left-most(Laura) and right-most(Robert)
>'Janet' = left most is ron, right most is robert
>'Andrew = left most is laura, right most is David

After re-arranging the lft and rgt values as above, it's not too hard
anymore:

SELECT o.member AS member, l.member AS leftmost, r.member AS rightmost
FROM hierarchy AS o
INNER JOIN hierarchy AS l
ON l.rgt = (SELECT MIN(rgt)
FROM hierarchy
WHERE lft BETWEEN o.lft AND o.rgt)
INNER JOIN hierarchy AS r
ON r.lft = (SELECT MAX(lft)
FROM hierarchy
WHERE lft BETWEEN o.lft AND o.rgt)
WHERE o.member IN ('nancy', 'janet', 'andrew')

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> I have implemented Joe Celko's model to store heirarchy and it
works well and nice I have a question about SQL <<

You might want to buy a copy of my book TREES & HIERARCHIES IN SQL :)

>> This is the actual table .. the Side column is to tell its left, or
right. This is a binary heirarchy. <<

I have a better model for binary trees! Remember "heapsort" from your
first data structures/algorithms class in college?

CREATE TABLE Heap
(member CHAR(15) NOT NULL,
place INTEGER NOT NULL PRIMARY KEY
CHECK(place > 1);

root = 1
place of left child of member (n) = (2*n)
place of right child of member (n) = (2*n +1)

Use integer division to travel toward the root of the tree.

('Somebody', 2, 3) is missing in the data you posted, but it looks
like ('nancy', 1) is the root of the tree.

>> 'Nancy', I need to find left-most(Laura) and right-most(Robert) <<

Is this correct? I keep going left (or right) until I get to a leaf
node in the tree or to a node without a left (or right) child:

A
/ \
/ \
B C
/ \
/ \
D E

leftmost(A) = B
rightmost(A) = E
leftmost(C) = D
rightmost(C) = E
leftmost(D) = NULL
rightmost(D) = NULL

So the leftmost(n) member is MAX(2* .. (2*n) ..) if the whole
generated set is in the heap. The rightmost(n) member is MAX(2* ..
(2*n+1) ..+1) +1) if the whole generated set is in the heap.

You can use a loop, recursion or a look-up table for the math.|||Saw the first part of your question on experts-exchange and asked a
buddy of mine (Corey Aldebol) how his tree model would work for your
problem. Take a look here for his reply:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41772

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Help with breaking out codes in a crystal report

Has anyone ever had a scenario where a calculations was showing totals but you had to break that down to the actual codes making up those totals?

For instance on a report, I have by Office by Employee totals for Hours and Wages but need a calculation of some kind to break those out to the individual codes and totals. how can I do that? See example below:

Current Report:

Naples Office Hours Dollars HrsTot $Tot

Smith, Tom 40:00 $400.00 40:00 $400.00

Desired Report:

Naples Office REG OT SICK Bonus Comm HrsTot $Tot

Smith, Tom 10:00 2:00 1:00 $20 $10 14:00 $30That information has to be available in your datasource. If so, you can change your groupings and such to get the information, but you might need a subreport if you want to display both together (depending on how the aggregations are happening).

Help with a query

Guys-

Any help with this query is greatly appreciated...

DECLARE @.input TABLE
(NodeId VARCHAR(10),
IsChecked CHAR(1))

DECLARE @.Actual TABLE
(AndSetId INT,
NodeId VARCHAR(10),
IsChecked CHAR(1))

INSERT INTO @.Input VALUES ('a', 'T')
INSERT INTO @.Input VALUES ('b', 'T')
INSERT INTO @.Input VALUES ('c', 'F')
INSERT INTO @.Input VALUES ('d', 'F')
INSERT INTO @.Input VALUES ('e', 'T')

INSERT INTO @.Actual VALUES (1, 'a', 'T')
INSERT INTO @.Actual VALUES (1, 'b', 'T')
INSERT INTO @.Actual VALUES (1, 'c', 'F')

INSERT INTO @.Actual VALUES (2, 'c', 'F')
INSERT INTO @.Actual VALUES (2, 'd', 'F')

INSERT INTO @.Actual VALUES (3, 'd', 'F')

INSERT INTO @.Actual VALUES (5, 'd', 'F')
INSERT INTO @.Actual VALUES (5, 'e', 'F')

INSERT INTO @.Actual VALUES (6, 'f', 'F')

INSERT INTO @.Actual VALUES (7, 'g', 'F')

-- We should get back 1, 2, 3, 4. We should not get back 5, 6, 7Did your instructor include the rules (the logic) behind this, or only give you the two inputs and the output that they expected? Have you recently covered anything like this in class?

-PatP|||Pat-

My apologies for not elaborating enough, what I was looking for.

Get all AndSetIds from @.Actual where

if NodeId is present in @.input then the isChecked should match.
eg: AndSetId 1
It has 3 records. And the records match with the records
in Input.
So 1 should be returned.

Where as let us look at AndSet 5.
It has NodeId 'e' as 'F' where as the input has 'e' as 'T'
So 5 should not be returned.

INSERT INTO @.Input VALUES ('a', 'T')
INSERT INTO @.Input VALUES ('b', 'T')
INSERT INTO @.Input VALUES ('c', 'F')
INSERT INTO @.Input VALUES ('d', 'F')
INSERT INTO @.Input VALUES ('e', 'T')

INSERT INTO @.Actual VALUES (1, 'a', 'T')
INSERT INTO @.Actual VALUES (1, 'b', 'T')
INSERT INTO @.Actual VALUES (1, 'c', 'F')

INSERT INTO @.Actual VALUES (2, 'c', 'F')
INSERT INTO @.Actual VALUES (2, 'd', 'F')

INSERT INTO @.Actual VALUES (3, 'd', 'F')

INSERT INTO @.Actual VALUES (5, 'd', 'F')
INSERT INTO @.Actual VALUES (5, 'e', 'F')

INSERT INTO @.Actual VALUES (6, 'f', 'F')

INSERT INTO @.Actual VALUES (7, 'g', 'F')|||Never mind. I think I have a solution. Thanks for your time Pat.sql

Help regarding sorting report

I have created als.rdlc file for my report it has data from the tableLS ( eg. Month , year, target, actual, etc ), and i have export thatLS.rdlc file in myreport.aspx by using ReportViewer from the toolbox. So when i complie that report.aspx it pull up the report. It pulls up all the data from LS but what i want is sort them by YEAR then MONTH, I Dont know how to do it.? Can any one help me???

Try adding an ORDER BY to the SELECT. If that does not work, change to using a stored procedure.|||I dont know how would you sort it? when i run the report it pulls up the whole database table , so how would i put a search in that?? I am not familer with sorting much.|||

Just change
SELECT A, B, C FROM TABLE

to
SELECT A, B, C FROM TABLE ORDER BY ID