SQLTeam.com | Weblogs | Forums

Stored procedure to distribute sales profit in hierarchy

tsql
sql2008

#1

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


#2

Please provide table descriptions of all tables involved (also @SalesDistribution) as create statement, sample data as insert statements, expected output (from the sample data you provide) and the entire sp + parameter values you call the sp with to get the result you want.