April 7, 2008 at 3:31 pm
I have the following code:
Declare @ConsultantID char(20)
Declare @StartDate dateTime
Declare @EndDate dateTime
Set @consultantID = '0000006'
Set @StartDate = '11/1/2007'
Set @EndDate = '10/31/2008'
Select v.ConsultantID
,ConsultantName
,Achievelevel
,AchieveTitle
,PeriodEndDate
,PurchaseAmount
,saleamountLevelone
,saleamountlevelTwo
,SaleamountLevelThree
,'TeamSalesPoint' =
Case
When AchieveLevel = 40 Then SUM(saleAmountLevelOne)/10000
When AchieveLevel = 50 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo)/30000
When AchieveLevel = 60 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)/110000
When AchieveLevel = 70 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)/250000
When AchieveLevel = 80 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)/500000
ELSE 0
END
,'TeamSalesLevelAmount' =
Case
When AchieveLevel = 40 Then '10000'
When AchieveLevel = 50 Then '30000'
When AchieveLevel = 60 Then '110000'
When AchieveLevel = 70 Then '250000'
When AchieveLevel = 80 Then '500000'
ELSE '10000'
END
,'TeamSalesAmount' =
Case
When AchieveLevel = 40 Then SUM(saleAmountLevelOne)
When AchieveLevel = 50 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo)
When AchieveLevel = 60 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)
When AchieveLevel = 70 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)
When AchieveLevel = 80 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)
ELSE 0
END
,'TeamSalesLevel' =
Case
When AchieveLevel = 40 Then '1st Line'
When AchieveLevel = 50 Then '1st and 2nd Line'
When AchieveLevel = 60 Then '1st, 2nd and 3rd Line'
When AchieveLevel = 70 Then '1st, 2nd and 3rd Line'
When AchieveLevel = 80 Then '1st, 2nd and 3rd Line'
ELSE '1st Line'
END
INTO #TeamSalesPoint FROM volume v
WHERE v.ConsultantID = @ConsultantID AND
v.PeriodEndDate >= @StartDate
AND v.PeriodEndDate <= @EndDate
AND v.AchieveLevel >=40
GROUP BY
v.ConsultantID
,ConsultantName
,Achievelevel
,AchieveTitle
,PeriodEndDate
,PurchaseAmount
,saleamountLevelone
,saleamountlevelTwo
,SaleamountLevelThree
Select t.ConsultantID
,AchieveTitle
,AchieveLevel
,SUM(TeamSalesPoint) AS TeamSalesPoint
,TeamSalesLevelAmount
,SUM(TeamSalesAmount) AS TeamSalesAmount
,TeamSalesLevel AS TeamSaleslevel
,'SrConsultant'=
CASE
WHEN SrConsultantDate >= @StartDate
AND SrConsultantDate <= @EndDate THEN 'Sr. Consultant *'
END
,'TeamLeader'=
CASE
WHEN TeamLeaderDate >= @StartDate
AND TeamLeaderDate <= @EndDate THEN 'Team Leader *'
END
,'TeamManager'=
CASE
WHEN TeamManagerDate >= @StartDate
AND TeamManagerDate <= @EndDate THEN 'Team Manager*'
END
,'SrTeamManager'=
CASE
WHEN SrTeamManagerDate >= @StartDate
AND SrTeamManagerDate <= @EndDate THEN 'Sr. Team Manager *'
END
,'TeamMentor'=
CASE
WHEN TeamMentorDate >= @StartDate
AND TeamMentorDate <= @EndDate THEN 'Team Mentor *'
END
,'SrTeamMentor'=
CASE
WHEN SrTeamMentorDate >= @StartDate
AND SrTeamMentorDate <= @EndDate THEN 'Sr. Team Mentor *'
END
FROM #TeamSalesPoint t
LEFT OUTER JOIN consultant c ON t.consultantID = c.consultantID
GROUP BY t.ConsultantID
,AchieveLevel
,AchieveTitle
,TeamSalesLevelAmount
,TeamSalesLevel
,TeamLeaderDate
,TeamManagerDate
,SrTeamManagerDate
,TeamMentorDate
,SrTeamMentorDate
,SrConsultantDate
Drop Table #TeamSalesPoint
What it should be doing is returning a single but it keeps returning two. The Position dates (SrConsultant, TeamLeader, etc.) should be NULL unless the date falls within @StartDateand @ENdDate parameters. Any ideas?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 7, 2008 at 4:31 pm
off hand i would say the problem is the group by.
you're including every date in the group by but i think you want to omit them and wrap the titles in a min().
,'TeamMentor'=
min(CASE
WHEN TeamMentorDate >= @StartDate
AND TeamMentorDate <= @EndDate THEN 'Team Mentor *'
END)
...
GROUP BY t.ConsultantID
,AchieveLevel
,AchieveTitle
,TeamSalesLevelAmount
,TeamSalesLevel
/*,TeamLeaderDate
,TeamManagerDate
,SrTeamManagerDate
,TeamMentorDate
,SrTeamMentorDate
,SrConsultantDate*/
April 8, 2008 at 9:04 am
I added the MIN as you suggested and it gives me a single record but none of the Case statements seem to work. I also tried it with MAX and then the Case seems to work but I get two records again.
Code:
Declare @ConsultantID char(20)
Declare @StartDate dateTime
Declare @EndDate dateTime
Set @consultantID = '0026551'
Set @StartDate = '11/1/2007'
Set @EndDate = '10/31/2008'
Select * from Consultant where TeamLeaderDate > '11/04/2007'
Select v.ConsultantID
,ConsultantName
,Achievelevel
,AchieveTitle
,PeriodEndDate
,PurchaseAmount
,saleamountLevelone
,saleamountlevelTwo
,SaleamountLevelThree
,'TeamSalesPoint' =
Case
When AchieveLevel = 40 Then SUM(saleAmountLevelOne)/10000
When AchieveLevel = 50 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo)/30000
When AchieveLevel = 60 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)/110000
When AchieveLevel = 70 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)/250000
When AchieveLevel = 80 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)/500000
ELSE 0
END
,'TeamSalesLevelAmount' =
Case
When AchieveLevel = 40 Then '10000'
When AchieveLevel = 50 Then '30000'
When AchieveLevel = 60 Then '110000'
When AchieveLevel = 70 Then '250000'
When AchieveLevel = 80 Then '500000'
ELSE '10000'
END
,'TeamSalesAmount' =
Case
When AchieveLevel = 40 Then SUM(saleAmountLevelOne)
When AchieveLevel = 50 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo)
When AchieveLevel = 60 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)
When AchieveLevel = 70 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)
When AchieveLevel = 80 Then SUM(saleAmountLevelOne+SaleAmountLevelTwo+SaleAmountLevelThree)
ELSE 0
END
,'TeamSalesLevel' =
Case
When AchieveLevel = 40 Then '1st Line'
When AchieveLevel = 50 Then '1st and 2nd Line'
When AchieveLevel = 60 Then '1st, 2nd and 3rd Line'
When AchieveLevel = 70 Then '1st, 2nd and 3rd Line'
When AchieveLevel = 80 Then '1st, 2nd and 3rd Line'
ELSE '1st Line'
END
INTO #TeamSalesPoint FROM volume v
WHERE v.ConsultantID = @ConsultantID AND
v.PeriodEndDate >= @StartDate
AND v.PeriodEndDate <= @EndDate
AND v.AchieveLevel >=40
GROUP BY
v.ConsultantID
,ConsultantName
,Achievelevel
,AchieveTitle
,PeriodEndDate
,PurchaseAmount
,saleamountLevelone
,saleamountlevelTwo
,SaleamountLevelThree
Select DISTINCT t.ConsultantID
,AchieveTitle
,AchieveLevel
,SUM(TeamSalesPoint) AS TeamSalesPoint
,TeamSalesLevelAmount
,SUM(TeamSalesAmount) AS TeamSalesAmount
,TeamSalesLevel AS TeamSaleslevel
,'SrConsultant'=
MAX(CASE
WHEN SrConsultantDate >= @StartDate
AND SrConsultantDate <= @EndDate THEN 'Sr. Consultant *'
ELSE ''
END)
,'TeamLeader'=
MAX(CASE
WHEN TeamLeaderDate >= @StartDate
AND TeamLeaderDate <= @EndDate THEN 'Team Leader *'
ELSE ''
END)
,'TeamManager'=
MAX(CASE
WHEN TeamManagerDate >= @StartDate
AND TeamManagerDate <= @EndDate THEN 'Team Manager*'
ELSE ''
END)
,'SrTeamManager'=
MAX(CASE
WHEN SrTeamManagerDate >= @StartDate
AND SrTeamManagerDate <= @EndDate THEN 'Sr. Team Manager *'
ELSE ''
END)
,'TeamMentor'=
MAX(CASE
WHEN TeamMentorDate >= @StartDate
AND TeamMentorDate <= @EndDate THEN 'Team Mentor *'
ELSE ''
END)
,'SrTeamMentor'=
MAX(CASE
WHEN SrTeamMentorDate >= @StartDate
AND SrTeamMentorDate <= @EndDate THEN 'Sr. Team Mentor *'
ELSE ''
END)
FROM #TeamSalesPoint t
LEFT OUTER JOIN consultant c ON t.consultantID = c.consultantID
GROUP BY t.ConsultantID
,AchieveLevel
,AchieveTitle
,TeamSalesLevelAmount
,TeamSalesLevel
Drop Table #TeamSalesPoint
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 8, 2008 at 10:13 am
you've already got a group by, so there's no reason to use DISTINCT. the statement below will only have 1 row per unique combination of t.ConsultantID, AchieveTitle, AchieveLevel, TeamSalesLevelAmount, and TeamSalesLevel.
Select t.ConsultantID,
AchieveTitle,
AchieveLevel,
SUM(TeamSalesPoint) AS TeamSalesPoint ,
TeamSalesLevelAmount,
SUM(TeamSalesAmount) AS TeamSalesAmount,
TeamSalesLevel,
MAX(CASE
WHEN SrConsultantDate BETWEEN @StartDate AND @EndDate
THEN 'Sr. Consultant *' ELSE ''
END) as SrConsultant,
... other consultant title cases ...
FROM #TeamSalesPoint t
LEFT OUTER JOIN consultant c ON t.consultantID = c.consultantID
GROUP BY t.ConsultantID,
AchieveTitle,
AchieveLevel,
TeamSalesLevelAmount,
TeamSalesLevel
btw, in your original post, the consultant title CASE statements did not include an ELSE. if the ELSE is omitted for a CASE statement, null is the result if no other no other conditions match and either min() or max() will produce the same results. since you added [font="Courier New"]ELSE ''[/font], only max() will produce the proper result.
April 8, 2008 at 10:34 am
I see the the issue, the achievelevel changes so how can I go about suppressing the second row?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 8, 2008 at 12:26 pm
With a little more input from BA I found myself going at this backward. In some cases I would be returning more than one row which leads to an issue with in the my main proc:
Code (see bold):
Declare @ConsultantID char(20)
Declare @StartDate dateTime
Declare @EndDate dateTime
Set @consultantID = '0026551'
Set @StartDate = '11/1/2007'
Set @EndDate = '10/31/2008'
IF @EndDate > GETDATE()
BEGIN
SET @EndDate = GETDATE()
END;
CREATE TABLE #Temp5 (
[OrigConsID] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrigConsName] [nvarchar](101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ConsultantID] [nchar](13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Downlinelevel] [int] NULL,
[ConsultantName] [nvarchar](101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SponsorName] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveDate] [datetime] NULL,
[SponsorID] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AchievedTitle] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AchieveDate] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BumpupDate] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ConsultantXID] [int] NULL,
[SponsorXID] [int] NULL,
[Active] [bit] NULL,
[StatusID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AgreementDate] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CurrentLevelXID] [int] NULL,
[DeactivationDate] [datetime] NULL,
[PurchaseAMount] [decimal](38, 2) NULL,
[PEriodENdDate] [datetime] NULL,
[AchieveLEvel] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ConsultantDate] [datetime] NULL
)
INSERT INTO #Temp5
EXEC consultantreports.uspS_ConsultantGetDownline @ConsultantID,@StartDate,@EndDate
SELECT [OrigConsID]
,[OrigConsName]
,[ConsultantID]
,[Downlinelevel]
,[ConsultantName]
,[SponsorName]
,[EffectiveDate]
,[SponsorID]
,[AchievedTitle]
,[AchieveDate]
,[BumpupDate]
,[ConsultantXID]
,[SponsorXID]
,[Active]
,[StatusID]
,[AgreementDate]
,[CurrentLevelXID]
,[DeactivationDate]
,[PurchaseAMount]
,[PEriodENdDate]
,[AchieveLEvel]
,[ConsultantDate]
,0 AS 'Points'
INTO #Temp6 from #Temp5 t
Update #Temp6
SET points = points + 1
FROM #Temp6 t
where t.ConsultantDate Between @StartDate and @EndDate
and t.active = 1
UPDATE t
SET points = points + 1
FROM #Temp6 t
INNER JOIN (SELECT ConsultantId
FROM volume
GROUP BY ConsultantId
HAVING SUM(PurchaseAmount) >= 1000) v
ON t.ConsultantId = v.ConsultantId
WHERE DeactivationDate BETWEEN @StartDate AND @EndDate
AND Active = 0
--Select * from #Temp6
CREATE TABLE #Temp1 (
id_num int IDENTITY(1,1),
COnsultantID char(20),
ConsultantNAme char(50),
SponsorID char(20),
RecruitCOunt INT,
AchieveDate DateTime,
PurchaseAMount DEC(19,2),
Active int,
Points int,
ConsultantDate DateTime,
DeactivationDate Datetime
)
INSERT iNTO #Temp1
Select r.ConsultantID, r.ConsultantName, r.SponsorID, Sum(r.Points) AS RecruitCOunt,r.AchieveDate,r.PurchaseAmount, r.Active,r.Points, r.ConsultantDate,r.DeactivationDate
FRom #Temp6 r
WHERE r.Points > 0 --or (r.Active=0 AND r.PurchaseAmount >=1000) -- I think this is where I need to calculate the lifetime sales which would be all records for this consultant in the volume table.
GROUp BY r.ConsultantID, r.SponsorID,r.achieveDate,r.ConsultantName, r.PurchaseAmount
,r.Active,r.Points,r.ConsultantDate,r.DeactivationDate
CREATE TABLE #teamsalespoint (
ConsultantID char(20),
AchieveTitle Char(20),
AchieveLevel Char(20),
TeamSAlesPoint int,
TeamSalesLevelAmount Char(20),
TeamSalesAmount DEC(19,2),
TeamSalesLevel CHAR(50),
[SrConsultant] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TeamLeader] [varchar](13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TeamManager] [varchar](13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SrTeamManager] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TeamMentor] [varchar](13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SrTeamMentor] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO #Teamsalespoint
EXEC consultantreports.uspS_CalcTeamSalesPoint @ConsultantID
--Select * from #TeamSalespoint
SELECT v.ConsultantID
,PurchaseAmount
,AchieveLevel
,MAX(c.NACDate) AS NACDate
,MAX(v.PeriodEndDate)AS PeriodEndDate
INTO #TotalPORSales FROM Volume v
LEFT OUTER JOIN Consultant c ON v.ConsultantID = c.ConsultantID
WHERE v.ConsultantID = @ConsultantID AND v.PeriodEndDate >= @StartDate
AND v.PeriodEndDate <= @EndDate
AND c.NacDate IS NOT NULL
GROUP BY v.ConsultantID
,AchieveLevel
--,c.NACDate
,PurchaseAmount
--Select * from #totalporSales
SELECT v.ConsultantID
,SUM(PurchaseAmount) AS PORSalesTotal
,'PORSalesPoints' =
CASE
WHEN SUM(PurchaseAmount) > 15000 And NACDATE <= '06/30/2008' then (select FLOOR(SUM(PurchaseAmount) / 15000))
WHEN SUM(PurchaseAmount) > 15000 And NACDATE > '06/30/2008' then (select FLOOR(SUM(PurchaseAmount) / 10000))
ELSE 0
END
,'Label' =
CASE
WHEN NACDATE =40 then '15000'
WHEN NACDATE > '06/30/2008'AND AchieveLevel >=40 then '10000'
WHEN AchieveLevel < 40 THEN '10000'
ELSE ''
END
,(SELECT ISNULL(SUM(RecruitCount),0) FROM #Temp1) AS RecruitPoints
,(Select TeamSalespoint from #Teamsalespoint) AS TeamSalesPoint
,(Select AchieveLevel from #Teamsalespoint) AS AchieveLevel
,(Select AchieveTitle from #Teamsalespoint) AS AchieveTitle
,(Select TeamSalesLevelAmount from #Teamsalespoint) AS TeamSaleslevelAmount
,(Select TeamSalesAmount from #Teamsalespoint) AS TeamSalesAmount
,(Select TeamSalesLevel from #Teamsalespoint) AS TeamSalesLevel
,(Select SrConsultant from #Teamsalespoint) AS SrConsultant
,(Select TeamLeader from #Teamsalespoint) AS TeamLeader
,(Select TeamManager from #Teamsalespoint) AS TeamManager
,(Select SrTeamManager from #Teamsalespoint) AS SrTeamManager
,(Select TeamMentor from #Teamsalespoint) AS TeamMentor
,(Select SrTeamMentor from #Teamsalespoint) AS SrTeamMentor,'LeaderShipPoints' =
CASe
WHEN (Select TeamSalespoint from #Teamsalespoint) <> 0 THEN (Select TeamSalespoint from #Teamsalespoint)+(SELECT SUM(RecruitCount) FROM #Temp1)
When (SELECT SUM(RecruitCount) FROM #Temp1) <> 0 THEN (SELECT SUM(RecruitCount) FROM #Temp1)+ (Select TeamSalespoint from #Teamsalespoint)
ELSE 0
END
--,v.AchieveLevel
,v.NACDate
--,MAx(v.PeriodEndDate)
FROM #TotalPORSales v
WHERE v.PeriodEndDate >= @StartDate
AND v.PeriodEndDate <= @EndDate
GROUP BY v.ConsultantID
,AchieveLevel
,v.NACDate
--,PurchaseAmount
--Order by v.Achievelevel Desc
DROP TABLE #TotalPORSales
DROP TABLE #TeamSalesPoint
DROP TABLE #Temp1
Drop Table #Temp5
Drop Table #Temp6
Because I could be returning multiple rows I need to adjust this part to accept that because right now I am getting:
Msg 512, Level 16, State 1, Line 145
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 8, 2008 at 2:05 pm
I did a join to the temp table to see if that would get rid of the SubQuery issue but now I get duplicate records:
SELECT v.ConsultantID
,SUM(PurchaseAmount) AS PORSalesTotal
,'PORSalesPoints' =
CASE
WHEN SUM(PurchaseAmount) > 15000 And NACDATE <= '06/30/2008' then (select FLOOR(SUM(PurchaseAmount) / 15000))
WHEN SUM(PurchaseAmount) > 15000 And NACDATE > '06/30/2008' then (select FLOOR(SUM(PurchaseAmount) / 10000))
ELSE 0
END
,'Label' =
CASE
WHEN NACDATE =40 then '15000'
WHEN NACDATE > '06/30/2008'AND v.AchieveLevel >=40 then '10000'
WHEN v.AchieveLevel < 40 THEN '10000'
ELSE ''
END
,(SELECT ISNULL(SUM(RecruitCount),0) FROM #Temp1) AS RecruitPoints
,t.TeamSalesPoint
----,T.AchieveTitle
,t.TeamSalesLevel
----,t.AchieveTitle
,T.TeamSalesLevelAmount
,t.SrConsultant
,t.TeamLeader
,t.TeamManager
,t.SrTeamManager
,t.TeamMentor
,t.SrTeamMentor
,'LeaderShipPoints' =
CASe
WHEN (SUM(t.TeamSalespoint)) <> 0 THEN (SUM(t.TeamSalespoint) )+(SELECT SUM(RecruitCount) FROM #Temp1)
When (SELECT SUM(RecruitCount) FROM #Temp1) <> 0 THEN (SELECT SUM(RecruitCount) FROM #Temp1)+ (SUM(t.TeamSalespoint))
ELSE 0
END
,v.AchieveLevel
,v.NACDate
--,MAx(v.PeriodEndDate)
FROM #TotalPORSales v
INNER JOIN #TeamSalesPoint t ON v.ConsultantID = t.ConsultantID
WHERE v.PeriodEndDate >= @StartDate
AND v.PeriodEndDate <= @EndDate
GROUP BY v.ConsultantID
,v.AchieveLevel
,v.NACDate
--,PurchaseAmount
,t.TeamSalesPoint
--,T.AchieveTitle
,t.TeamSalesLevel
,t.SrConsultant
,t.TeamLeader
,t.TeamManager
,t.SrTeamManager
,t.TeamMentor
,t.SrTeamMentor
,T.TeamSalesLevelAmount
DROP TABLE #TotalPORSales
DROP TABLE #TeamSalesPoint
DROP TABLE #Temp1
Drop Table #Temp5
Drop Table #Temp6
Recordset ( there should only be two records):
ConsultantID PORSalesTotal PORSalesPoints Label RecruitPoints TeamSalesPoint TeamSalesLevel TeamSalesLevelAmount SrConsultant TeamLeader TeamManager SrTeamManager TeamMentor SrTeamMentor LeaderShipPoints AchieveLevel NACDate
-------------------- --------------------------------------- --------------------------------------- ----- ------------- -------------- -------------------------------------------------- -------------------- ---------------- ------------- ------------- ------------------ ------------- ----------------- ---------------- ------------ -----------------------
0026551 2032.37 0 15000 1 0 1st and 2nd Line 30000 Team Manager* Sr. Team Manager * Team Mentor * 1 40 1995-01-01 00:00:00.000
0026551 2032.37 0 15000 1 0 1st Line 10000 Team Manager* Sr. Team Manager * Team Mentor * 1 40 1995-01-01 00:00:00.000
0026551 5664.66 0 15000 1 0 1st and 2nd Line 30000 Team Manager* Sr. Team Manager * Team Mentor * 1 50 1995-01-01 00:00:00.000
0026551 5664.66 0 15000 1 0 1st Line 10000 Team Manager* Sr. Team Manager * Team Mentor * 1 50 1995-01-01 00:00:00.000
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 8, 2008 at 2:53 pm
Art,
You've got way too much going on here. You should simplify it.
'PORSalesPoints' = CASE
WHEN SUM(PurchaseAmount) > 15000 And NACDATE <= '06/30/2008'
THEN (select FLOOR(SUM(PurchaseAmount) / 15000))
WHEN SUM(PurchaseAmount) > 15000 And NACDATE > '06/30/2008'
THEN (select FLOOR(SUM(PurchaseAmount) / 10000))
ELSE 0
END
In this CASE, SUM(PurchaseAmount) is divided by a different figure based on NACDATE. This is simpler:
'PORSalesPoints' =
IFNULL(FLOOR( SUM(PurchaseAmount) /
CASE
WHEN NACDATE <= '06/30/2008' THEN 15000
WHEN NACDATE > '06/30/2008' THEN 10000
ELSE NULL
END ),0)
If NACDATE is not nullable, it can simlified further:
'PORSalesPoints' = FLOOR( SUM(PurchaseAmount) /
CASE WHEN NACDATE <= '06/30/2008'
THEN 15000 ELSE 10000
END )
Is NACDATE a datetime or smalldatetime? If so, why are you comparing it to 40?
'Label' = CASE
WHEN NACDATE =40 then '15000'
WHEN NACDATE > '06/30/2008'AND v.AchieveLevel >=40 then '10000'
WHEN v.AchieveLevel < 40 THEN '10000'
ELSE ''
END
If 1900-02-10 is a magic date in your system, you should use that date for the comparison and not 40. But I really think you mean NACDATE <= '06/30/2008'. If so, this CASE is suspiciously similar to the 'PORSalesPoints' CASE (determining 15000 or 10000 based on NACDATE) but throws AchieveLevel into the mix. Should AchieveLevel be considered for 'PORSalesPoints' also?
Here's another CASE that should be simplified:
'LeaderShipPoints' =
CASE
WHEN (SUM(t.TeamSalespoint)) <> 0
THEN (SUM(t.TeamSalespoint))+(SELECT SUM(RecruitCount) FROM #Temp1)
WHEN (SELECT SUM(RecruitCount) FROM #Temp1) <> 0
THEN (SELECT SUM(RecruitCount) FROM #Temp1)+(SUM(t.TeamSalespoint))
ELSE 0
END
Basically this CASE boils down to
if (A <> 0) then A+B
else if (B <> 0) then B+A
else 0
So, the CASE is pointless and can be replaced with:
'LeaderShipPoints' =
ISNULL(SUM(t.TeamSalespoint) + (SELECT SUM(RecruitCount) FROM #Temp1),0)
Lastly, I think you're getting the unexpected rows because each ConsultantId has achieved multiple sales levels: '1st and 2nd line' and '1st Line'. I assume that '1st and 2nd line' includes '1st line' so the fix is to eliminate TeamSalesLevel from the select and group by and use this CASE statement from your earlier post.
CASE max(AchieveLevel)
WHEN 40 Then '1st Line'
WHEN 50 Then '1st and 2nd Line'
WHEN 60 Then '1st, 2nd and 3rd Line'
WHEN 70 Then '1st, 2nd and 3rd Line'
WHEN 80 Then '1st, 2nd and 3rd Line'
ELSE 'TeamSalesLevel'
END as TeamSalesLevel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply