June 30, 2012 at 12:11 pm
i just tried like this
;WITH MemberAndParent
AS (
SELECT m.MemberID, m.Name AS MemberName, r.ParentId
FROM tblMembers m
LEFT JOIN
tblMembersRelation r
ON r.MemberID = m.MemberID
),
Hierarchy
AS (
SELECT ParentId, MemberID, 0 AS LevelNum, MemberName, CAST(MemberName AS VARCHAR(1000)) AS SortOrder
FROM MemberAndParent
WHERE MemberId=1
UNION ALL
SELECT p.ParentId, p.MemberID, LevelNum + 1 AS LevelNum, p.MemberName, CAST(h.SortOrder + '|' + p.MemberName AS VARCHAR(1000)) AS SortOrder
FROM MemberAndParent p
JOIN Hierarchy h
ON h.MemberID = p.ParentId
)
INSERT INTO @solution3 ( LevelNum,MemberID,ParentId,MemberName)
SELECT
H.LevelNum,
H.MemberID,
H.ParentId,
H.MemberName
FROM Hierarchy H
ORDER BY LevelNum,ParentId
select
t1.levelnum as Memberlevel ,
t1.MemberID,
t1.ParentId,
t1.MemberName,
pinfo.ProductId,
pinfo.ProjectId,
pinfo.SaleMemberId,
pinfo.SaleClosedPrice,
pinfo.SaleDate,
[OwnCommission],
[ChildCommission],
(case WHEN [OwnCommission] is null THEN [ChildCommission] ELSE [OwnCommission] END) 'CommissionAmount'
from
@solution3 t1
inner join
tblProductInfo pinfo
on t1.MemberID=pinfo.SaleMemberId
CROSS APPLY (SELECT [OwnCommission] = CASE WHEN LevelNum = 0 THEN pinfo.SaleClosedPrice * 6/100 END) x
CROSS APPLY (SELECT [ChildCommission] = CASE WHEN LevelNum >= 1 THEN pinfo.SaleClosedPrice* 2/100 ELSE 0 END) y
order by SaleDate desc
;
for this i will get only solution for member 1 but i need solution for all member id
This is perfect query for your requirement.All you have to do is to tweak it a little bit based on your requirement.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
June 30, 2012 at 5:45 pm
sivag (6/29/2012)
for example member 6 soled product 500he will get comission of 6% of 30 as comission
his parent id 4 will receive comission of 2% of 10 as comission
the member 4 has a parent id 2 will receive comission of 2% of 10 as comission
the member 2 has a parent id 1 will receive comission of 2% of 10 as comission
This looks like the beginning of a payout schedule for a "Uni-Level" Multi-Level Marketing (MLM) company like Amway and some of the other companies in the United States.
I appreciate your test data but let's get this right. What is the FULL payout schedule? I've seen some companies with as many as 9 payout levels. How many does yours have? Also, how deep is the hierarchy? In this case, I want to know how many levels deep the actual hierarchy is. I've seen some that are more than a thousand levels deep and just need to know how deep your's might be within the next 10 years.
Here's what you've identified as the payout schedule, so far.
Level Payout%
X-3 2% --The person below reports to this person and this person gets 2% of the sale made at Level "X".
X-2 2% --The person below reports to this person and this person gets 2% of the sale made at Level "X".
X-1 2% --The person below reports to this person and this person gets 2% of the sale made at Level "X".
X 6% --Note that this is the person who made the actual sale.
Is that it or does it go deeper? Are there any prequalifiers for the commision payouts? By that I mean, does someone have to have so many people in the "downline" to get paid? Do they personally have to sell a certain amount on their own before they're eligible to receive any commisions from the sales that occur in their "downline"?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2012 at 8:41 am
is this anywhere near what you want?
CREATE TABLE [dbo].[tblMembersRelation](
[MembersRelationID] [int] NOT NULL,
[MemberID] [int] NULL,
[ParentID] [int] NULL,
[Level] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblProductInfo](
[ProductId] [int] NOT NULL,
[ProjectId] [int] NOT NULL,
[SaleMemberId] [int] NULL,
[SaleClosedPrice] [decimal](18, 4) NULL,
[SaleDate] [datetime] NULL,
[SaleCreateDate] [datetime] NULL,
) ON [PRIMARY]
GO
INSERT INTO [dbo].[tblProductInfo]([ProductId], [ProjectId], [SaleMemberId], [SaleClosedPrice], [SaleDate], [SaleCreateDate])
SELECT 1, 1, 6, 3000000.0000, '20120101 00:00:00.000', '20120101 00:00:00.000' UNION ALL
SELECT 2, 1, 4, 1000000.0000, '20120201 00:00:00.000', '20120201 00:00:00.000'
GO
INSERT INTO [dbo].[tblMembersRelation]([MembersRelationID], [MemberID], [ParentID], [Level])
SELECT 1, 1, NULL, NULL UNION ALL
SELECT 2, 2, 1, NULL UNION ALL
SELECT 3, 3, 1, NULL UNION ALL
SELECT 4, 4, 2, NULL UNION ALL
SELECT 5, 5, 3, NULL UNION ALL
SELECT 6, 6, 4, NULL
GO
;WITH ctePC
AS (SELECT MemberID,
ParentID,
PCL = 1,
OM = memberID
FROM tblMembersRelation
WHERE MemberID IN( SELECT DISTINCT SaleMemberId
FROM tblProductInfo
)
UNION ALL
SELECT a.MemberID,
a.ParentID,
PCL = PCL + 1,
b.OM
FROM tblMembersRelation a
INNER JOIN ctePC b ON a.MemberID = b.ParentID)
,
ctePCSELECT AS ( SELECT MemberID,
ParentID,
PCL,
OM
FROM ctePC
WHERE PCL < 5
)
SELECT ctePCSELECT.OM,
tblProductInfo.SaleClosedPrice,
ctePCSELECT.MemberID,
ctePCSELECT.PCL,
CASE WHEN PCL = 1 THEN SaleClosedPrice* .06 ELSE SaleClosedPrice* .02 END AS COMMISSION,
CASE WHEN PCL = 1 THEN '6%' ELSE '2%' END AS PERCENTAGE
FROM tblProductInfo
INNER JOIN ctePCSELECT ON tblProductInfo.SaleMemberId = ctePCSELECT.OM
ORDER BY ctePCSELECT.OM,
ctePCSELECT.PCL
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 3, 2012 at 2:19 pm
Hi Sivag
would be interested to know if and how you manged to resolve your problem?
regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 3, 2012 at 10:37 pm
hi
J Livingston
sorry for the twodays i was admitted in a hospital due to an injury on my right leg now only i came back i saw ur post
thanks for u r help
July 4, 2012 at 9:21 am
I'm still curious about the payout schedule I asked about before.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 4, 2012 at 11:49 am
sivag (7/3/2012)
hiJ Livingston
sorry for the twodays i was admitted in a hospital due to an injury on my right leg now only i came back i saw ur post
thanks for u r help
really sorry to hear you have been in hospital and hope you are making a quick recovery.
I am not clear if any of the responses you have been given are working for you and would like to know how you have solved your problem.....I have a potentially similar problem that is likely to come up in the next few weeks...hence my interest.
When you are able to do so...pls post back.
many thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 5, 2012 at 12:13 am
this is question which u asked
Level Payout%
X-3 2% --The person below reports to this person and this person gets 2% of the sale made at Level "X".
X-2 2% --The person below reports to this person and this person gets 2% of the sale made at Level "X".
X-1 2% --The person below reports to this person and this person gets 2% of the sale made at Level "X".
X 6% --Note that this is the person who made the actual sale.
Is that it yes thats all for example the total commission level will be splitted to 3 parent id when a person sale a product he will get 6% here x sale a product
for 3level (x-1,x-2,x-3) they will get 2%
another example i can tell u for example if member 1 sale a product means he will get 6% and he is not having parent id null so there wont be 2%
and u asked this question
s that it or does it go deeper? Are there any prequalifiers for the commision payouts? By that I mean, does someone have to have so many people in the "downline" to get paid? Do they personally have to sell a certain amount on their own before they're eligible to receive any commisions from the sales that occur in their "downline"?
for this kind of level seperaton i asked another question in this link
http://www.sqlservercentral.com/Forums/Topic1317024-391-1.aspx
July 5, 2012 at 12:50 am
J Livingston SQL your proc was working fine and i am not finding exact member level of that member i will post the proc my combining level of the member
July 5, 2012 at 12:04 pm
sivag (7/5/2012)
J Livingston SQL your proc was working fine and i am not finding exact member level of that member i will post the proc my combining level of the member
does this look any better ...will give you what I think you want as 'member level'
;WITH ctePC
AS (SELECT MemberID,
ParentID,
PCL = 1,
OM = memberID
FROM tblMembersRelation
WHERE MemberID IN( SELECT DISTINCT SaleMemberId
FROM tblProductInfo
)
UNION ALL
SELECT a.MemberID,
a.ParentID,
PCL = PCL + 1,
b.OM
FROM tblMembersRelation a
INNER JOIN ctePC b ON a.MemberID = b.ParentID)
,
ctePCSELECT AS ( SELECT MemberID,
ParentID,
PCL,
OM
FROM ctePC
WHERE PCL < 5
)
SELECT
--ctePCSELECT.OM,
ctePCSELECT.MemberID,
CASE
WHEN PCL = 1 THEN 'X'
WHEN PCL = 2 THEN 'X-1'
WHEN PCL = 3 THEN 'X-2'
WHEN PCL = 4 THEN 'X-3'
END AS LEVEL,
tblProductInfo.SaleClosedPrice,
--ctePCSELECT.PCL,
CAST(CASE WHEN PCL = 1 THEN SaleClosedPrice* .06 ELSE SaleClosedPrice* .02 END AS DECIMAL(12,2)) AS COMMISSION,
CASE WHEN PCL = 1 THEN '6%' ELSE '2%' END AS PERCENTAGE
FROM tblProductInfo
INNER JOIN ctePCSELECT ON tblProductInfo.SaleMemberId = ctePCSELECT.OM
ORDER BY ctePCSELECT.OM,
ctePCSELECT.PCL
MemberID LEVEL SaleClosedPrice COMMISSION PERCENTAGE
----------- ----- --------------------------------------- --------------------------------------- ----------
4 X 1000000.0000 60000.00 6%
2 X-1 1000000.0000 20000.00 2%
1 X-2 1000000.0000 20000.00 2%
6 X 3000000.0000 180000.00 6%
4 X-1 3000000.0000 60000.00 2%
2 X-2 3000000.0000 60000.00 2%
1 X-3 3000000.0000 60000.00 2%
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply