June 29, 2012 at 12:37 am
here iam 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
;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 = 1
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,ParentID,
MemberID,
CAST(REPLICATE(' | ', LevelNum - 1) + MemberName AS VARCHAR(100)) AS MemberName
FROMHierarchy
ORDER BY LevelNum
;
output willl be
LevelNumParentIDMemberIDMemberName
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
June 29, 2012 at 5:19 am
June 29, 2012 at 5:25 am
anthony.green that was different concept and this was a different concept
in that concept i will provide parameter @memberid but in this iam not using that parameter
June 29, 2012 at 5:38 am
Sorry, looks very similar to each other.
June 29, 2012 at 5:45 am
plz watch the out put of both which i asked
June 29, 2012 at 6:02 am
in that proc
m
m1 m2 level1
m1.1 m1.2 m2.1 m2.2 level2
in that proc if m loged in means if m1,m2m1.m1.1 , m1.2 , m2.1,m2.2 have made a sale mean he will receive commission of 2% from the saleclosed price
if m2 loged in means if m2.1,m2.2 have made a sale mean he will receive commission of 2% from the saleclosed price
but in this proc no member loged in admin only watch will get logged in it will static member level as m1 is the level 1the admin will watch all the members in sentive commision in a single page
June 29, 2012 at 9:17 am
hi lynn can u please try to help me
June 29, 2012 at 9:50 am
From what I have seen of this, I just don't have 20 to 40 hours of free time to this for nothing. If I'm going to spend this kind of time on a problem, it is either for something I am doing for myself. If you have $1500 to $3000 you want to spend for this help, that is another story.
I can help more people with other problems that won't take the effort this problem will.
June 29, 2012 at 12:18 pm
can any one plz try to help me
June 29, 2012 at 12:21 pm
i love to help those who help themselves;
it looks like Chris@home spent a lot of time writing code and helping you out there in the other thread, but i don't see anything based off of that work in this post; it's like you didn't understand his posts and wanted to start fresh instead.
can you show us a query you have that almost...but not quite...gets the data you are looking for?
if you don't build off of the previous help you've already received, how can we help you?
Lowell
June 29, 2012 at 12:38 pm
If others write all your code, how do you plan on supporting it if something goes wrong? Lowell is right, you need to start doing this and then asking for specific help when you run into a problem and have trouble solvinga specific piece.
June 29, 2012 at 11:57 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
June 30, 2012 at 4:13 am
sivag (6/29/2012)
anthony.green that was different concept and this was a different conceptin that concept i will provide parameter @memberid but in this iam not using that parameter
And yes, this appears to be the same concept.
You just need to figure out where to put the @memberid for the query.
June 30, 2012 at 5:09 am
m1 level o
m2 m3 level1
m4 m5 m6 m7 level2
m8 m9 m10 m11 m12 m13 . m14. m15 level3
m14 m15 level4
for example in a produt sale
if member14 sale a product rs 300000
for that the commission for him is 6% and the parent id m8 will rececive 2% commission and the parent id m4 will rececive 2% commission
and the parent id m2 will rececive 2% commission
similarly
if member4 sale a product rs 100000
for that the commission for him is 6% and the parent id m2 will rececive 2% commission and the parent id m1 will rececive 2% commission
salememberid saleclosed price memberereceivecommission percentage commission
m14 300000 m14 6% 180000
m14 300000 m8 2% 6000
m14 300000 m4 2% 6000
m14 300000 m2 2% 6000
m4 100000 m4 6% 6000
m4 100000 m2 2% 2000
m4 100000 m1 2% 2000
only it sale commission will be given for 3 level num parent id only
here i didnt pass any member id the only palce i have to gine member id=1 for searching all level
June 30, 2012 at 11:58 am
sivag (6/30/2012)
m1 level o
m2 m3 level1
m4 m5 m6 m7 level2
m8 m9 m10 m11 m12 m13 . m14. m15 level3
m14 m15 level4
for example in a produt sale
if member14 sale a product rs 300000
for that the commission for him is 6% and the parent id m8 will rececive 2% commission and the parent id m4 will rececive 2% commission
and the parent id m2 will rececive 2% commission
similarly
if member4 sale a product rs 100000
for that the commission for him is 6% and the parent id m2 will rececive 2% commission and the parent id m1 will rececive 2% commission
salememberid saleclosed price memberereceivecommission percentage commission
m14 300000 m14 6% 180000
m14 300000 m8 2% 6000
m14 300000 m4 2% 6000
m14 300000 m2 2% 6000
m4 100000 m4 6% 6000
m4 100000 m2 2% 2000
m4 100000 m1 2% 2000
only it sale commission will be given for 3 level num parent id only
here i didnt pass any member id the only palce i have to gine member id=1 for searching all level
Sorry, but this appears to be incomplete.
It would help if you kept your text separate from the code instead of embedding them together.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply