Can any one help me to write the code for count left and right position and calculate

[srikrrishna]

hi friends i create one table as below

CREATE TABLE [dbo].[regi](

	[id] [int] IDENTITY(1,1) NOT NULL,
	[sponserid] [varchar](50) NULL,
	[introducername] [varchar](50) NULL,
	[placedunderid] [varchar](50) NULL,
	[name] [varchar](50) NULL,
	[so] [varchar](50) NULL,
	[position] [int] NOT NULL,
	[gender] [int] NOT NULL,
	[occupation] [varchar](50) NULL,
	[dob] [varchar](50) NULL,
	[addr] [varchar](200) NULL,
	[area] [varchar](50) NULL,
	[city] [varchar](50) NULL,
	[district] [varchar](50) NULL,
	[state] [varchar](50) NULL,
	[pincode] [varchar](50) NULL,
	[email] [varchar](50) NULL,
	[landline] [varchar](50) NULL,
	[mobileno] [varchar](50) NULL,
	[pwd] [varchar](50) NULL,
	[pin] [varchar](50) NULL,
	[nominee] [varchar](50) NULL,
	[relation] [varchar](50) NULL,
	[age] [varchar](50) NULL,
	[bankpayeename] [varchar](50) NULL,
	[accno] [varchar](50) NULL,
	[bankname] [varchar](50) NULL,
	[branch] [varchar](50) NULL,
	[ifsc] [varchar](50) NULL,
	[createddate] [datetime] NULL) ON [PRIMARY]GO SET ANSI_PADDING OFF GO

1. in this table sponserid is the parent of the table.
2. name is the child of the parent.
3. position indicates left and right.
4. here condition is parent must have 2 childs (**left and right only ).
5. For the first parent have to left + right+ left (or) left +right + right then it calculate to 500 rs for the parent after every child add 250 rs

Can any one help me to write the code for count left and right position and calculate rupees for particular child.
==

if 3 childs in one parent then we have to give the comission 500 and after for every child comission increase to 250(500 + 250 = 750)

[Rabbit]

Please use code tags when posting code or formatted data.

I don't know what you're talking about. Are you referring to some sort of binary tree? Some sample data and results would help us understand your question better.

[srikrrishna]

Dear rabbit

yes i am generating a tree .
my condition is:
if 3 childs in one parent then we have to give the commission 500 and after for every child commission increase to 250(500 + 250 = 750)

and i tried this in query..

i created regi table

CREATE TABLE [dbo].[regi](
[id] [int] IDENTITY(1,1) NOT NULL,
[sponserid] [varchar](50) NULL,
[introducername] [varchar](50) NULL,
[placedunderid] [varchar](50) NULL,
[name] [varchar](50) NULL,
[so] [varchar](50) NULL,
[position] [int] NOT NULL,
[gender] [int] NOT NULL,

[createddate] [datetime] NULL
) ON [PRIMARY]

GO

and position table
as

id position
1 left
2 right


here what i want is

if 3 childs in one parent then we have to give the commission 500 and after for every child commission increase to 250(500 + 250 = 750)

Mohan(parent)
|
---------------------------
| |
krish(child-left) phani(child-right)
|
------------------
|
mani(child-left)

[Rabbit]

That is not something that is easy to do with the data structure you have chosen. You will need to create a special function that will recursively traverses the tree until it finds all the children nodes.

http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

[Rabbit]

Alternatively, you could change your structure to a nested set model.

https://en.wikipedia.org/wiki/Nested_set_model