im creating a web application.when we create a new user under the reference of existing user ,the query should return the commision to admin after giving all parent nodes their commision share.
my query is given below....its only return hirarchy from the current user to root...need to add commisiondivision between nodes...
ALTER PROCEDURE [dbo].[spUser_Save]
@PKUserId int,
@FirstName varchar(100),
@LastName varchar(100),
@DOB datetime,
@ReferId int,
@Count int,
@EmailId varchar(100),
@Password varchar(100),
@Address varchar(250),
@Landmark varchar(100),
@City varchar(100),
@PostOffice varchar(100),
@District varchar(100),
@PinCode varchar(10),
@State varchar(100),
@PhoneNum varchar(20),
@MobileNum varchar(20),
@FKRowStatusID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @referCount int;
DECLARE @Mytable table(UserId int,ReferId int,Username varchar(100),Depth int);
SELECT @referCount= [COUNT] FROM tblUser U WHERE U.UserId=CASE
WHEN @ReferId =0
THEN U.UserId
ELSE @ReferId
END
IF(@referCount<4)
BEGIN
INSERT INTO tblUser VALUES(@FirstName,@LastName,@DOB,@ReferId,@Count,@EmailId,@Password,@Address,@Landmark,
@City,@PostOffice,@District,@PinCode,@State,@PhoneNum,@MobileNum,@FKRowStatusID,GETDATE());
UPDATE tblUser SET Count=tblUser.Count +1
WHERE tblUser.UserId=CASE
WHEN @ReferId =0
THEN tblUser.UserId
ELSE @ReferId
END ;
END
ELSE IF(@referCount>=4)
BEGIN
DECLARE @StartID INT = 11
;WITH c
AS
(
SELECT UserId,ReferId,FirstName, 1 AS Depth
FROM tblUser
WHERE ReferId=@ReferId
UNION ALL
SELECT t.UserId,t.ReferId,t.FirstName,c.Depth + 1 AS 'Level'
FROM tblUser T
INNER JOIN c ON t.UserId = c.ReferId
)
INSERT INTO @Mytable SELECT *
FROM c Group BY c.UserId,c.ReferId,c.FirstName,c.Depth ORDER BY Depth;
SELECT * FROM @Mytable;
END
SELECT SCOPE_IDENTITY();
END
the attachment contains manual calculation