here i am having 3 table member table ,member relation table,and tblProductInfo depend upon the sale in product info
the member relation table the amount is calculated
DROP TABLE [dbo].[tblMembers]
CREATE TABLE [dbo].[tblMembers](
[MemberID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](30) NULL)
INSERT INTO [tblMembers] ([Name])
VALUES ('siva'), ('ganesh'), ('rajesh'), ('gobi'), ('arun'), ('rajesharavind')
DROP TABLE [dbo].[tblMembersRelation]
CREATE TABLE [dbo].[tblMembersRelation](
[MembersRelationID] [int] IDENTITY(1,1) NOT NULL,
[MemberID] [int] NULL,
[ParentID] [int] NULL,
[Level] [int] NULL)
INSERT INTO [tblMembersRelation] ([MemberID],[ParentID])
VALUES (1, null ), (2, 1), (3, 1), (4, 2), (5, 3), (6, 4)
DROP TABLE [dbo].[tblProductInfo]
CREATE TABLE [dbo].[tblProductInfo](
[ProductId] [int] IDENTITY(1,1) NOT NULL,
[ProjectId] [int] NOT NULL,
[SaleMemberId] [int] NULL,
[SaleClosedPrice] [decimal](18, 4) NULL,
[SaleDate] [datetime] NULL,
[SaleCreateDate] [datetime] NULL)
INSERT INTO [tblProductInfo] ([ProjectId],[SaleMemberId],[SaleClosedPrice],[SaleDate])
VALUES (1,6,500,'6-31-2012')
here to find the maximum level i just used this select poc
;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, 1 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
)
SELECT LevelNum,ParentID,
MemberID,
CAST(REPLICATE(' | ', LevelNum - 1) + MemberName AS VARCHAR(100)) AS MemberName
FROM Hierarchy
ORDER BY LevelNum
;
for example if member soled the product means is commission was 6 % parent memberid of a member under the member control means the member will get 2% commission
for example member 6 soled product 500 he 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
sivaganesh12...
Member
227 Points
308 Posts
how to split the incentive for a member and his team leader ,maneger for admin view?
Jun 29, 2012 08:37 AM|LINK
here i am having 3 table member table ,member relation table,and tblProductInfo depend upon the sale in product info
the member relation table the amount is calculated
DROP TABLE [dbo].[tblMembers] CREATE TABLE [dbo].[tblMembers]( [MemberID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](30) NULL) INSERT INTO [tblMembers] ([Name]) VALUES ('siva'), ('ganesh'), ('rajesh'), ('gobi'), ('arun'), ('rajesharavind') DROP TABLE [dbo].[tblMembersRelation] CREATE TABLE [dbo].[tblMembersRelation]( [MembersRelationID] [int] IDENTITY(1,1) NOT NULL, [MemberID] [int] NULL, [ParentID] [int] NULL, [Level] [int] NULL) INSERT INTO [tblMembersRelation] ([MemberID],[ParentID]) VALUES (1, null ), (2, 1), (3, 1), (4, 2), (5, 3), (6, 4) DROP TABLE [dbo].[tblProductInfo] CREATE TABLE [dbo].[tblProductInfo]( [ProductId] [int] IDENTITY(1,1) NOT NULL, [ProjectId] [int] NOT NULL, [SaleMemberId] [int] NULL, [SaleClosedPrice] [decimal](18, 4) NULL, [SaleDate] [datetime] NULL, [SaleCreateDate] [datetime] NULL) INSERT INTO [tblProductInfo] ([ProjectId],[SaleMemberId],[SaleClosedPrice],[SaleDate]) VALUES (1,6,500,'6-31-2012')here to find the maximum level i just used this select poc ;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, 1 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 ) SELECT LevelNum,ParentID, MemberID, CAST(REPLICATE(' | ', LevelNum - 1) + MemberName AS VARCHAR(100)) AS MemberName FROM Hierarchy ORDER BY LevelNum ;for example if member soled the product means is commission was 6 %
parent memberid of a member under the member control means the member will get 2% commission
for example member 6 soled product 500
he 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
i am trying out put
Chen Yu - MS...
All-Star
21569 Points
2493 Posts
Microsoft
Re: how to split the incentive for a member and his team leader ,maneger for admin view?
Jul 04, 2012 09:22 AM|LINK
Hi,
Please check below query, it works fine.
DROP TABLE tblMembers DROP TABLE tblMembersRelation DROP TABLE tblProductInfo create TABLE tblMembers ( [MemberID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](30) NULL) INSERT INTO tblMembers ([Name]) VALUES ('siva'), ('ganesh'), ('rajesh'), ('gobi'), ('arun'), ('rajesharavind') create TABLE tblMembersRelation ( [MembersRelationID] [int] IDENTITY(1,1) NOT NULL, [MemberID] [int] NULL, [ParentID] [int] NULL, [Level] [int] NULL) INSERT INTO tblMembersRelation ([MemberID],[ParentID]) VALUES (1, null ), (2, 1), (3, 1), (4, 2), (5, 3), (6, 4) create TABLE tblProductInfo ( [ProductId] [int] IDENTITY(1,1) NOT NULL, [ProjectId] [int] NOT NULL, [SaleMemberId] [int] NULL, [SaleClosedPrice] [decimal](18, 4) NULL, [SaleDate] [datetime] NULL, [SaleCreateDate] [datetime] NULL) INSERT INTO tblProductInfo ([ProjectId],[SaleMemberId],[SaleClosedPrice],[SaleDate]) VALUES (1,5,500,'6-30-2012') INSERT INTO tblProductInfo ([ProjectId],[SaleMemberId],[SaleClosedPrice],[SaleDate]) VALUES (1,6,500,'6-30-2012') declare @currentID int declare @parentID int declare @members_id int DECLARE @tempTable TABLE (memberid int,data int) select @members_id = count(SaleMemberId) from tblProductInfo while (@members_id > 0) begin select @currentID = SaleMemberId from tblProductInfo where ProductId = @members_id set @parentID = @currentID insert into @tempTable(memberid ,data) select @currentID,@parentID while( @parentID!=1) begin select @parentID = [ParentID] FROM [dbo].[tblMembersRelation] Where [MembersRelationID] = @parentID insert into @tempTable (memberid ,data) select @currentID,@parentID end set @members_id = @members_id -1 end select * from @tempTable ;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, 1 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 ) select convert(Date,t3.SaleDate, 102) as Date, t3.ProjectId,t3.ProductId,t3.SaleClosedPrice,t3.SaleMemberId ,t1.Name,t4.data as memeberID, LevelNum as Memberlevel , case when (T3.SaleMemberId = t4.data) then (t3.SaleClosedPrice * 0.06) else (t3.SaleClosedPrice*0.02) end as commisson , case when (T3.SaleMemberId = t4.data) then 6 else 2 end as 'per%' from tblMembers t1 inner join tblProductInfo t3 on t1.MemberID = t3.SaleMemberId inner join @tempTable t4 on t4.memberid = t3.SaleMemberId inner join Hierarchy on Hierarchy.memberid = t4.data --result Date ProjectId ProductId SaleClosedPrice SaleMemberId Name memeberID Memberlevel commisson per% ---------- ----------- ----------- --------------------------------------- ------------ ------------------------------ ----------- ----------- --------------------------------------- ----------- 2012-06-30 1 2 500.0000 5 arun 1 1 10.000000 2 2012-06-30 1 2 500.0000 5 arun 3 2 10.000000 2 2012-06-30 1 2 500.0000 5 arun 5 3 30.000000 6 2012-06-30 1 1 500.0000 6 rajesharavind 1 1 10.000000 2 2012-06-30 1 1 500.0000 6 rajesharavind 2 2 10.000000 2 2012-06-30 1 1 500.0000 6 rajesharavind 4 3 10.000000 2 2012-06-30 1 1 500.0000 6 rajesharavind 6 4 30.000000 6Thanks.
Feedback to us
Develop and promote your apps in Windows Store