Database

ALTER PROCEDURE [dbo].[SearchEmpRecords_Sp2]

   @SearchBy     varchar(50) = '',
   @SearchVal    varchar(50) = ''		
AS
BEGIN                   
DECLARE <a href="/Members/value">@Value</a> varchar(50)		             
 SET @SearchBy = @SearchBy + ','	  
 IF REPLACE (@SearchBy, ',', 'ALL') <> ''				
  BEGIN		
   SET <a href="/Members/value">@Value</a> = (@SearchBy)
    IF <a href="/Members/value">@Value</a> <> ''
  BEGIN
 SELECT EmployeeID,Username, State, AreaNumber FROM Employees
 WHERE

*** The line below is what I'm trying to get to work. 
*** Whenever I ran this line I get my custom error saying:
Records Not Found

--    <a href="/Members/value">@Value</a>  LIKE '%'  + @SearchVal + '%' ----

*** These lines work but I would like to replace them with the line above ***

      Username LIKE '%'  + @SearchVal + '%'
      OR State LIKE '%' + @SearchVal + '%'
      OR AreaNumber LIKE '%' + @SearchVal + '%'
END					
END
END

TASK Table
----------
Task_ID        Name            Allocated_To        etc
1              Fix XYZ Bug     Dan                 ...
2              Add ABC Feature Dan                 ...

CATEGORY Table
--------------
Cat_ID         Name            Description         etc
1              Bug             This is a bug       ...
2              Feature         This is a feature   ...
3              Work            This is a work item ...

TASK_CATEGORY Table
-------------------
Task_Cat_ID    Task_ID         Cat_ID
1              1               1
2              1               3
3              2               2
4              2               3

TASK Table
----------
Task_ID        Name            Categories          Allocated_To        etc
1              Fix XYZ Bug     1;3                 ...
2              Add ABC Feature 2;3                 ...

CATEGORY Table (as before)

 hi friends actually i am working on a mlm project in which members are added in a tree pattern, and get the payment accordingly.

My table  structure is as follow:

        Id         ParentId        IsLeft           IsRight
         1          Null             Null             Null
         2           1                1               Null
         3           1               Null               1
         4           2                1               Null
         5           2               Null               1
         6           3                1               Null
         7           3               Null               1
         8           4                1               Null
         9           4               Null               1
         10          5                1               Null

the problem is that initially 1500$ are given to parent when two nodes are added to its left and one to his right(2:1) . and then 500$ for each pair.

My problem is to find the query which can return the total income of any given node.

enter image description here

According to figure node 1 must get 2500$ (1500+500+500) first 500$ is for node 4 and second 500$ is for node 3.

According to figure node 2 must get 1500$ because it has two nodes to its left and one node to its right this means a ratio of (2:1). and has no pairs

According to figure node 3 must get 0$ because it does not have any nodes in ratio(2:1)

one thing has to be kept in mind that 1500$ will be the first payment and then only the other pairs will be counted, and 1500$ will be given when node has ratio 2:1(two nodes on left and one on right) but no money when ratio is 1:2(one node on left and two on right)

I have found the query which will count all the pairs below a particular node and give receiving amount according to 500$, but the query has not been able to consider the first condition that is the 2:1 condition

        declare @ParentId as int
         set @ParentId=1

       create table #temp_table_name
        (
          ParentId varchar(30) null,
         )


      ;with Child as
       (
    select id,ParentId from tblTestingTree where id=@ParentId
    union all
    Select tblTestingTree.Id,tblTestingTree.parentId from tblTestingTree 
    inner join Child 
    on tblTestingTree.ParentId=Child.Id
       )

  insert into #temp_table_name
  select c.ParentId  from tblTestingTree T join Child c
  on c.Id=t.Id
  WHERE ISNULL(T.ParentId, 0) <> 0 and c.ParentId!=@ParentId
  group by c.ParentId
  having COUNT(c.ParentId)>1 

  select COUNT(*)*500 as totalmoney from #temp_table_name

  drop table #temp_table_name