I am trying to write a store procedure to distribute sales profit among the UserID in a given hierarchy above the user who does a sale
This is the code which i had written
BEGIN
DECLARE @SaleDistribution TABLE
(
PackID INT NULL,
SalesUserID VARCHAR(10) NULL,
RowLevel smallint NULL,
Createdon Datetime NULL,
ActualPercentage Decimal(18,5) NULL,
RelevantPercentage Decimal(18,5) NULL,
Commision Decimal(18,5) NULL,
PackRate Decimal(18,5) NULL,
PreviousBalance Decimal(18,5) NULL,
ParentID VARCHAR(10) NULL,
IsActive bit NULL,
IsBalanceUpdate bit NULL,
ExtraPackAmount Decimal(18,5) NULL
)
DECLARE @PackID VARCHAR(10) = '886',
@PackRate DECIMAL(18,2)
SELECT @PackRate = PM.Price FROM UserPack US INNER JOIN PackageMaster PM ON US.PackageID=PM.PackageID where US.PackID= @PackID
DECLARE @RowNum INT = 1
DECLARE @id BIGINT
DECLARE @Count INT
SET @id = 118
;WITH tblParent AS
(
SELECT id, userid, assignto FROM UserMaster WHERE Id = @id
UNION ALL
SELECT um.id, um.userid, um.assignto FROM Usermaster um JOIN tblParent ON um.userId = tblParent.assignto
)
SELECT userid, assignto into #temp FROM tblParent order by assignto asc
Set @Count = (Select Count(UserID) from #temp)
while @RowNum <= @Count
BEGIN
Insert into @SaleDistribution (PackID,SalesUserID,RowLevel,Createdon,ActualPercentage, PackRate,PreviousBalance, ParentID, ExtraPackAmount)
SELECT @PackID as PackID, temp.UserID as SalesUserID, @RowNum as RowLevel, Getdate() as CreatedOn,
ISnULL(tcm.Commision,'0.0000'),
@PackRate as PackRate,
SAM.Balance as PreviousBalance, AssignTo as ParentID, (us.PaymentAmount - @PackRate) as ExtraPackAmount from
(SELECT userid, Assignto, ROW_NUMBER() OVER (ORDER BY assignto asc) RowNum FROM #temp)temp Left Join SalesAccountbalance SAM ON SAM.UseriD = temp.UserID
Left join CommMaster tcm on tcm.userid = temp.userid
LEFT JOIN Packtransaction st on st.salesuserid = @id
LEFT JOIN UserPack us on us.PackID = @PackID and st.salesuserid = @id
WHERE RowNum = @RowNum
group by tcm.Commision,temp.userid, SAM.Balance, temp.Assignto,us.PaymentAmount,temp.RowNum
SET @RowNum = @RowNum + 1
END
select * from @SaleDistribution
Drop table #temp
END
The output what i am expecting is as below
PackID SalesUserID RowLevel Createdon ActualPercentage RelevantPercentage Commision SubscriptionRate PreviousBalance ParentID IsActive IsBalanceUpdate ExtraPackageAmount
886 1 1 2015-11-09 14:17:04.260 0.00000 50.00000 17500.0000 35000.00000 12501.50000 0 NULL NULL NULL
886 22 2 2015-11-09 14:17:04.260 0.00000 0.0000 0.0000 35000.00000 -2000.00000 1 NULL NULL NULL
886 24 3 2015-11-09 14:17:04.260 0.00000 0.0000 0.0000 35000.00000 NULL 22 NULL NULL NULL
886 118 4 2015-11-09 14:17:04.260 50.00000 50.0000 17500.0000 35000.00000 16050.00000 24 NULL NULL NULL
But the Output i am getting is as below
PackID SalesUserID RowLevel Createdon ActualPercentage RelevantPercentage Commision SubscriptionRate PreviousBalance ParentID IsActive IsBalanceUpdate ExtraPackageAmount
886 1 1 2015-11-09 14:17:04.260 0.00000 NULL NULL 35000.00000 -4998.50000 0 NULL NULL NULL
886 22 2 2015-11-09 14:17:04.260 0.00000 NULL NULL 35000.00000 -2000.00000 1 NULL NULL NULL
886 24 3 2015-11-09 14:17:04.260 0.00000 NULL NULL 35000.00000 NULL 22 NULL NULL NULL
886 118 4 2015-11-09 14:17:04.260 50.00000 NULL NULL 35000.00000 -1450.00000 24 NULL NULL NULL
Kindly guide where am i going wrong