ASP.NET

how to split the incentive for a member and his team leader ,maneger for admin view? RSS

1 reply

Last post Jul 04, 2012 09:22 AM by Chen Yu - MSFT

‹ Previous Thread|Next Thread ›

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

;

output  willl be 
LevelNum	ParentID	MemberID	MemberName
1	     NULL            	1	      siva
2	       1	              2	               | ganesh
2	       1	              3	               | rajesh
3	       3	              5	               |  | arun
3	       2	             4	               |  | gobi
4	       4	            6	                |  |  | rajesharavind

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

Date   ProjectId  ProductId  Closing Price SaleMemberId SaleMemberName  memberid Memberlevel commisson  per%

6-31-2012  1         1          500       6      'rajesharavind            1         1         10       2
6-31-2012  1         1          500       6      'rajesharavind            2         2         10       2
6-31-2012  1         1          500       6      'rajesharavind            4         3         10       2

6-31-2012  1         1          500       6      'rajesharavind            6         4         30       6

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                               6

Thanks.

Please mark the replies as answers if they help or unmark if not.
Feedback to us

Develop and promote your apps in Windows Store

Marked as answer by Young Yang - MSFT on Jul 10, 2012 02:20 PM