June 25, 2012 at 3:06 am
CREATE TABLE [dbo].[tblMembers](
[MemberID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](30) NULL
)
CREATE TABLE [dbo].[tblMembersRelation](
[MembersRelationID] [int] IDENTITY(1,1) NOT NULL,
[MemberID] [int] NULL,
[ParentID] [int] NULL,
[Level] [int] NULL
)
CREATE TABLE [dbo].[tblProductInfo](
[ProducttId] [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 [tblMembers] ([Name])
VALUES('siva'),
('ganesh'),
('rajesh'),
('gobi'),
('arun'),
('rajesharavind')
INSERT INTO [tblMembersRelation] ([MemberID],[ParentID])
VALUES(1 ,null ),
(2, 1),
(3, 1),
(4, 2),
(5, 3),
(6, 4)
INSERT INTO [tblProductInfo] ([ProducttId],[ProjectId],[SaleMemberId],[SaleClosedPrice],[SaleDate],[SaleCreateDate])
value(1,1,1,1000,'6-25-2012')
(2,1,2,500,'6-25-2012')
(3,1,4,500,'6-25-2012')
the solutionto find level
DECLARE@MemberID INTEGER = 1
;WITHMemberAndParent
AS(
SELECTm.MemberID, m.Name AS MemberName, r.ParentID
FROMtblMembers m
LEFT JOIN tblMembersRelation r
ONr.MemberID = m.MemberID
),
Hierarchy
AS(
SELECTParentID, MemberID, 1 AS LevelNum, MemberName, CAST(MemberName AS VARCHAR(1000)) AS SortOrder
FROMMemberAndParent
WHEREMemberID = @MemberID
UNIONALL
SELECTp.ParentID, p.MemberID, LevelNum + 1 AS LevelNum, p.MemberName, CAST(h.SortOrder + '|' + p.MemberName AS VARCHAR(1000)) AS SortOrder
FROMMemberAndParent p
JOINHierarchy h
ONh.MemberID = p.ParentID
)
SELECTLevelNum,
MemberID,
CAST(REPLICATE(' | ', LevelNum - 1) + MemberName AS VARCHAR(100)) AS MemberName
FROMHierarchy
ORDER BY SortOrder
;
output willl be
LevelNum MemberID MemberName
----------- ----------- ----------------------------------------------------------------------------------------------------
1 1 siva
2 2 | ganesh
3 4 | | gobi
4 6 | | | rajesharavind
2 3 | rajesh
3 5 | | arun
from this select query we can find the level of the member
for example if member one is login means
for example if member soled the product means is commission was 6 %
when ohter member soled product under the member control means the member will get 2% commission
under member 1 member 2and4 also there they sold one product each for that member one will get commision of 2%
in the tblProductInfo sale member id is a foregin key of member id
i am expecting output of commision for member1
Date ProjectId ProductId Closing Price SaleMemberId SaleMemberName Memberlevel commisson
'6-25-2012 1 1 1000 1 siva 1 60
'6-25-2012' 1 2 500 2 ganesh 2 10
'6-25-2012' 1 3 500 4 gobi 3 10
June 25, 2012 at 3:48 am
Please make sure your DDL/DML statements actually do work before posting them here.
A few questions:
How much commission does Siva get for selling ProductID = 1?
How much commission does Siva get for Ganesh selling ProductID = 2?
How much commission does Siva get for Gobi selling ProductID = 3?
How much commission does Ganesh get for selling ProductID = 2?
How much commission does Ganesh get for Gobi selling ProductID = 3?
How much commission does Gobi get for selling ProductID = 3?
Corrected DDL/DML (please check they do what you intend)
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],[SaleCreateDate])
VALUES (1,1,1000,'6-25-2012', GETDATE()), (1,2,500,'6-24-2012', GETDATE()), (1,4,500,'6-23-2012', GETDATE())
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2012 at 4:06 am
hi
let me tell u clearly the manager siva only get commison
when the member levelnumber is 1 if he sale the product means he will receive commision of 6% from the closed sale
and second point is Siva get for Ganesh selling ProductID = 2 he will receive commision of 2% from the closed sale
and the third point is Siva get for Gobi selling ProductID = 3 he will receive commision of 2% from the closed sale
we dont want get commssion amount for gobi and ganesh th comission amount calculted only for siva and ganesh and gobi are under siva
select inf.SaleCreateDate
,inf.Projectid
,inf.ProductId
,inf.SaleClosedPrice
,inf.SaleMemberId
from
dbo.tblProducttInfo inf
where
inf.SaleMemberId=(here i just place member id from the secect query which i wrote to find temporary table)
June 25, 2012 at 4:16 am
Try this. I've left in several columns to show how the query works, and omitted the final aggregate of the results.
DECLARE@MemberID INTEGER = 1
;WITH
MemberAndParent AS (
SELECT m.MemberID, m.Name AS MemberName, r.ParentID, p.ProductID, p.SaleClosedPrice
FROM tblMembers m
LEFT JOIN tblMembersRelation r
ONr.MemberID = m.MemberID
LEFT JOIN tblProductInfo p ON p.SaleMemberId = m.MemberID
),
Hierarchy AS (
SELECT ParentID, MemberID, 1 AS LevelNum, MemberName,
ProductID, SaleClosedPrice,
CAST(MemberName AS VARCHAR(1000)) AS SortOrder
FROM MemberAndParent
WHERE
MemberID = @MemberID -- Siva only
-- ParentID IS NULL -- collect all top-level parents
UNIONALL
SELECT p.ParentID, p.MemberID, LevelNum + 1 AS LevelNum, p.MemberName,
p.ProductID, p.SaleClosedPrice,
CAST(h.SortOrder + '|' + p.MemberName AS VARCHAR(1000)) AS SortOrder
FROM MemberAndParent p
JOIN Hierarchy h
ON h.MemberID = p.ParentID
)
SELECT
LevelNum,
MemberID,
sortorder,
ProductID, SaleClosedPrice,
CAST(REPLICATE(' | ', LevelNum - 1) + MemberName AS VARCHAR(100)) AS MemberName,
[Commission] = CASE WHEN LevelNum = 1 THEN 6 ELSE 2 END
FROM Hierarchy
WHERE ProductID IS NOT NULL
--ORDER BY SortOrder
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2012 at 4:40 am
in final u just mentioned
CAST(REPLICATE(' | ', LevelNum - 1) + MemberName AS VARCHAR(100)) AS MemberName,
[Commission] = CASE WHEN LevelNum = 1 THEN 6 ELSE 2 END
i need 6% comisson for member 1 as closed value was 1000 howto caluclate and from other
only 2% of commisson for member1 as closed value was 500 like this
Date ProjectId ProductId Closing Price SaleMemberId SaleMemberName Memberlevel commisson
'6-25-2012 1 1 1000 1 siva 1 60
'6-25-2012' 1 2 500 2 ganesh 2 10
'6-25-2012' 1 3 500 4 gobi 3 10
i dont no how to take percentgate i think for 1000 = 60 rupee is the 6 % i think and similarly for 500 2%is 10 i think
June 25, 2012 at 4:42 am
sivag (6/25/2012)
in final u just mentionedCAST(REPLICATE(' | ', LevelNum - 1) + MemberName AS VARCHAR(100)) AS MemberName,
[Commission] = CASE WHEN LevelNum = 1 THEN 6 ELSE 2 END
i need 6% comisson for member 1 as closed value was 1000 howto caluclate and from other
only 2% of commisson for member1 as closed value was 500 like this
Date ProjectId ProductId Closing Price SaleMemberId SaleMemberName Memberlevel commisson
'6-25-2012 1 1 1000 1 siva 1 60
'6-25-2012' 1 2 500 2 ganesh 2 10
'6-25-2012' 1 3 500 4 gobi 3 10
I'm sorry I don't understand your calculation, please elaborate.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2012 at 4:58 am
the commission will be place depen upon closed price
from the closed price of the sale only the commission placed
if the manager it soled one product means 6% will be commision amount for that product clossed value was 1000 for member 1 sold for that 6% was his commision amount
and if his team member sold a product means
for example 2 and 4 sold each one product rs 500 for two product
for that the manager will recieve 2% of two time becoz two member sold two product of closed value 500
i just edited in the above reply also
June 25, 2012 at 5:09 am
sivag (6/25/2012)
the commission will be place depen upon closed pricefrom the closed price of the sale only the commission placed
if the manager it soled one product means 6% will be commision amount for that product clossed value was 1000 for member 1 sold for that 6% was his commision amount
and if his team member sold a product means
for example 2 and 4 sold each one product rs 500 for two product
for that the manager will recieve 2% of two time becoz two member sold two product of closed value 500
i just edited in the above reply also
I'm sorry, there's a translation issue here and I cannot understand what you are trying to say. Perhaps someone else can help.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2012 at 5:29 am
sorry nothing else if a member sold a product means he will receive 6% comission
for example siva sold a product at a rate of 1000 for that he has to receive comission of 6% of the rate he saled the product product 1
eg:
comission amount =(1000\100)*6=60
and he also receive insentive of 2%
from 2 comission amount =(500\100)*2=10
from 4 comission amount =(500\100)*2=10
the expected output
Date ProjectId ProductId Closing Price SaleMemberId SaleMemberName Memberlevel commisson
'6-25-2012 1 1 1000 1 siva 1 60
'6-25-2012' 1 2 500 2 ganesh 2 10
'6-25-2012' 1 3 500 4 gobi 3 10
June 25, 2012 at 5:51 am
SaleClosePrice and Commission% are both returned by me last query. I can't understand where you are having a problem - surely all you have to do is multiply the two?
DECLARE@MemberID INTEGER = 1
;WITH
MemberAndParent AS (
SELECT m.MemberID, m.Name AS MemberName, r.ParentID, p.ProductID, p.SaleClosedPrice
FROM tblMembers m
LEFT JOIN tblMembersRelation r
ONr.MemberID = m.MemberID
LEFT JOIN tblProductInfo p ON p.SaleMemberId = m.MemberID
),
Hierarchy AS (
SELECT ParentID, MemberID, 1 AS LevelNum, MemberName,
ProductID, SaleClosedPrice,
CAST(MemberName AS VARCHAR(1000)) AS SortOrder
FROM MemberAndParent
WHERE
MemberID = @MemberID -- Siva only (ParentID IS NULL -- collect all top-level parents)
UNIONALL
SELECT p.ParentID, p.MemberID, LevelNum + 1 AS LevelNum, p.MemberName,
p.ProductID, p.SaleClosedPrice,
CAST(h.SortOrder + '|' + p.MemberName AS VARCHAR(1000)) AS SortOrder
FROM MemberAndParent p
JOIN Hierarchy h
ON h.MemberID = p.ParentID
)
SELECT
LevelNum,
MemberID,
sortorder,
ProductID, SaleClosedPrice,
CAST(REPLICATE(' | ', LevelNum - 1) + MemberName AS VARCHAR(100)) AS MemberName,
[Commission%] = CASE WHEN LevelNum = 1 THEN 6 ELSE 2 END
FROM Hierarchy
WHERE ProductID IS NOT NULL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2012 at 6:02 am
in the out put column i have to get this value
commisson
60
10
10
from your query i receive commison only
commisson
6
2
2
i have do mathematical proration in case function
comission amount =(1000\100)*6=60
and he also receive insentive of 2%
from 2 comission amount =(500\100)*2=10
from 4 comission amount =(500\100)*2=10
June 25, 2012 at 6:24 am
Change the final select to this:
SELECT
LevelNum,
MemberID,
sortorder,
ProductID, SaleClosedPrice,
CAST(REPLICATE(' | ', LevelNum - 1) + MemberName AS VARCHAR(100)) AS MemberName,
[Commission%],
[Incentive%]
FROM Hierarchy
CROSS APPLY (SELECT [Commission%] = CASE WHEN LevelNum = 1 THEN 6 ELSE 0 END) x
CROSS APPLY (SELECT [Incentive%] = CASE WHEN LevelNum > 1 THEN 2 ELSE 0 END) y
WHERE ProductID IS NOT NULL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 30, 2012 at 4:11 am
Not creating the Tree Graph, but I was able to create the following based on the posts above.
/****** Object: Table [dbo].[tblMembers] Script Date: 06/30/2012 03:43:43 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblMembers]') AND type in (N'U'))
DROP TABLE [dbo].[tblMembers]
GO
CREATE TABLE [dbo].[tblMembers](
[MemberID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](30) NULL
);
GO
INSERT INTO [tblMembers] ([Name])
VALUES
('siva'),
('ganesh'),
('rajesh'),
('gobi'),
('arun'),
('rajesharavind');
GO
/****** Object: Table [dbo].[tblMembersRelation] Script Date: 06/30/2012 03:44:06 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblMembersRelation]') AND type in (N'U'))
DROP TABLE [dbo].[tblMembersRelation]
GO
CREATE TABLE [dbo].[tblMembersRelation](
[MembersRelationID] [int] IDENTITY(1,1) NOT NULL,
[MemberID] [int] NULL,
[ParentID] [int] NULL,
[Level] [int] NULL
);
GO
INSERT INTO [tblMembersRelation] ([MemberID],[ParentID])
VALUES
(1, null ),
(2, 1),
(3, 1),
(4, 2),
(5, 3),
(6, 4);
GO
/****** Object: Table [dbo].[tblProductInfo] Script Date: 06/30/2012 03:44:33 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblProductInfo]') AND type in (N'U'))
DROP TABLE [dbo].[tblProductInfo]
GO
CREATE TABLE [dbo].[tblProductInfo](
[ProductId] [int] IDENTITY(1,1) NOT NULL,
[ProjectId] [int] NOT NULL,
[SaleMemberId] [int] NULL,
[SaleClosedPrice] [decimal](18, 2) NULL,
[SaleDate] [date] NULL,
[SaleCreateDate] [datetime] NULL
);
GO
INSERT INTO [tblProductInfo] (
[ProjectId],
[SaleMemberId],
[SaleClosedPrice],
[SaleDate]
)
VALUES
(1,6,500,cast('20120630' as date)),
(1,1,1000,cast('20120625' as date)),
(1,2,500,cast('20120624' as date)),
(1,4,500,cast('20120623' as date));
GO
with MemberSales (
MemberID,
Name,
ParentID,
ProjectID,
SaleDate,
SaleClosedPrice,
MemberLevel
) as (
select
m.MemberID,
m.Name,
mr.ParentID,
tpi.ProjectID,
tpi.SaleDate,
tpi.SaleClosedPrice,
1 as MemberLevel
from
dbo.tblMembers m
inner join dbo.tblMembersRelation mr
on (m.MemberID = mr.MemberID)
inner join dbo.tblProductInfo tpi
on (m.MemberID = tpi. SaleMemberID)
union all
select
m.MemberID,
m.Name,
mr.ParentID,
ms.ProjectID,
ms.SaleDate,
ms.SaleClosedPrice,
ms.MemberLevel + 1
from
dbo.tblMembers m
inner join dbo.tblMembersRelation mr
on (m.MemberID = mr.MemberID)
inner join MemberSales ms
on (m.MemberID = ms.ParentID)
)
select
MemberID,
Name,
ParentID,
ProjectID,
SaleDate,
SaleClosedPrice,
cast(SaleClosedPrice * case when MemberLevel = 1 then 0.06 else 0 end as decimal(10,2)) as Commission,
cast(SaleClosedPrice * case when MemberLevel > 1 then 0.02 else 0 end as decimal(10,2)) as Incentive
from
MemberSales
order by
SaleDate,
MemberID desc;
GO
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply