May 14, 2008 at 2:36 pm
i have the following code that should return 1 record per different ConsultantID and the latest effective date but end up with duplicate Consultants with different effective date. What am I doing wrong with the MAX function?
Declare @ConsultantID char(20)
Declare @StartDate Datetime
Declare @EndDate DateTime
Set @ConsultantID = '0000112'
Set @StartDate = '01/01/2008'
Set @EndDate = '06/30/2008';
With downline (ConsultantID,EffectiveDate, ConsultantName,SponsorID,SponsorName,DownLineLevel,
ConsultantXId,DeactivationDate,Bumpupdate,NACDate, CurrentLevelXID, StatusID,ConsultantDate)
AS
(
-- Anchor member defintion
SELECTA.ConsultantID
,MAX(A.EffectiveDate) as EffectiveDate
,A.FirstName + ' ' + A.LastName as ConsultantName
,A.[1stSponsorID] as SponsorID
,'' As SponsorName
,0 as DownLineLevel
,MAX(A.XID) as ConsultantXID
--,A.SponsorXID
--,A.Active
,A.InactiveDate AS DeactivationDate
,A.Bumpup as BumpupDate
,A.NACDate
--,A.CurrentLevelAchieveDate
,A.CompLevelID AS CurrentLevelXID
,A.GenealogyStatusID AS StatusID
,A.CNSDate
FROM consultant.consultantIntegration A with (nolock)
WHERE A.ConsultantID = @ConsultantID
AND a.EffectiveDate Between @StartDate and @EndDate
GROUP BY a.ConsultantID
,a.FirstName
,a.Lastname
,a.[1stSponsorId]
,a.InactiveDate
,a.Bumpup
,a.Nacdate
,a.ComplevelID
,a.GenealogyStatusID
,a.CNSDate
UNION ALL
--Recursive member definition
SELECTA.ConsultantID
,A.EffectiveDate
,A.FirstName + ' ' + A.LastName as ConsultantName
,A.[1stSponsorID] as SponsorID
,'' As SponsorName
,DownLineLevel + 1
,A.XID as ConsultantXID
--,A.SponsorXID
--,A.Active
,A.InactiveDate AS DeactivationDate
,A.Bumpup as BumpupDate
,A.NACDate
--,A.CurrentLevelAchieveDate
,A.CompLevelID AS CurrentLevelXID
,A.GenealogyStatusID AS StatusID
,A.CNSDate
FROM consultant.consultantIntegration AS A with (nolock)
INNER JOIN DownLine AS B ON
A.[1stSponsorID] = B.ConsultantID
WHERE a.EffectiveDate Between @StartDate and @EndDate
AND a.GenealogyStatusID NOT IN ( 'INACTIVE','NEW','HOMEOFFICE','DECEASED')
--AND A.StatusID NOT IN ('Inactive')
) -- Appropriate records
Select ConsultantID,
EffectiveDate,
ConsultantName,
SponsorID,
SponsorName,
DownLineLevel,
ConsultantXId,
DeactivationDate,
Bumpupdate,
NACDate,
CurrentLevelXID,
StatusID,
ConsultantDate
from Downline
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
May 14, 2008 at 4:25 pm
Art, if you run just your Anchor Member Definition with the same variable values do you get multiple rows? My first thought is that the GROUPing you are using is yielding multiple results.
Can you post sample data/DDL so we can reproduce it?
May 15, 2008 at 2:07 am
Hi;
In the group by clause , you used multiple columns and so the max function must return value for each row in the output. The problem is with the group by clause so you have to change the query.
GROUP BY a.ConsultantID
,a.FirstName
,a.Lastname
,a.[1stSponsorId]
,a.InactiveDate
,a.Bumpup
,a.Nacdate
,a.ComplevelID
,a.GenealogyStatusID
,a.CNSDate
September 3, 2009 at 9:08 am
Art,
I know this post is after the fact, but I thought I would put in my two cents in case someone else views this thread. As Hasan mentioned, the problem lies with the group by clause. If you want the max effective date to be per consultant, you will need to separate these values from the rest of the values in the query. Otherwise you will be returning the max effective date based on the consultant along with every other field being returned, which is bound to return multiple results. Something like the following should get you what you need:
SELECT
B.ConsultantID,
B.EffectiveDate,
A.FirstName + ' ' + A.LastName as ConsultantName,
A.[1stSponsorID] as SponsorID,
'' As SponsorName,
0 as DownLineLevel,
MAX(A.XID) as ConsultantXID
--,A.SponsorXID
--,A.Active,
A.InactiveDate AS DeactivationDate,
A.Bumpup as BumpupDate,
A.NACDate,
--A.CurrentLevelAchieveDate,
A.CompLevelID AS CurrentLevelXID,
A.GenealogyStatusID AS StatusID,
A.CNSDate
From
consultant.consultantIntegration A with (NOLOCK)
JOIN
(
SELECT
ConsultantID,
MAX(EffectiveDate) as EffectiveDate
FROM
consultant.consultantIntegration with (NOLOCK)
WHERE
A.ConsultantID = @ConsultantID
AND a.EffectiveDate Between @StartDate and @EndDate
)B on A.ConsultantID = B.ConsultantID
And A.EffectiveDate = B.EffectiveDate
WHERE
A.ConsultantID = @ConsultantID
AND a.EffectiveDate Between @StartDate and @EndDate
GROUP BY
B.ConsultantID,
B.EffectiveDate,
A.FirstName,
A.Lastname,
A.[1stSponsorId],
A.InactiveDate,
A.Bumpup,
A.Nacdate,
A.ComplevelID,
A.GenealogyStatusID,
A.CNSDate
Bob Pinella
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply