April 17, 2008 at 7:13 am
I am having a issue with returning s single row based on a MAX(EffectiveDate). Every time I run this code:
Select DISTINCT t.ConsultantID,
t.ConsultantName,
t.CurrentLevelXID,
MAX(t.EffectiveDate) AS EffectiveDate,
s.Title,
t.NACDate,
t.BillToState,
t.HomePhone,
t.SponsorXID,
t.SponsorID,
c.FirstName + ' ' + c.LastName AS SponsorName,
t.DeactivationDate,
t.Active
,t.ReactivateDt
from #Temp t
Left Outer Join Consultant c ON t.SponsorXID = c.ConsultantXID
LEFT Outer JOin sharedDimension..DimConsultantTitle s ON t.CurrentLevelXID = s.XID
WHERE t.BillToState = @State
OR (@State IS NULL)
GROUP BY
t.ConsultantID
,t.ConsultantName
--,c.EffectiveDate
,t.CurrentLevelXID
,s.Title
,T.NACDate
,t.BillToState
,t.HomePhone
,t.SponsorXID
,t.DeactivationDate
,t.Active
,T.SponsorID
,t.ReactivateDt
,c.FirstName
,c.LastName
Drop Table #Temp
It returns multipe rows. It's like ignoring my MAX(EffectiveDate) and returning all the dates instead of only the last date:
ConsultantID ConsultantName CurrentLevelXID EffectiveDate Title NACDate BillToState HomePhone SponsorXID SponsorID SponsorName DeactivationDate Active ReactivateDt
------------- ------------------------------ --------------- ----------------------- -------------------- ----------------------- ----------- -------------------- ----------- ------------- ------------------------------ ----------------------- ------ -----------------------
0010410 MARY M LOUKS 20 2007-12-03 11:26:44.263 Consultant 2002-08-26 00:00:00.000 MN 218/759-2747 126970 999999999-6 COMPRESSED ACCOUNT 2004-01-01 00:00:00.000 0 1899-12-30 00:00:00.000
0010410 MARY M LOUKS 50 2007-12-04 13:58:43.000 Team Manager 2002-08-26 00:00:00.000 MN 218/759-2747 126970 999999999-6 COMPRESSED ACCOUNT 2004-01-01 00:00:00.000 0 1899-12-30 00:00:00.000
0010414 LAURA A KNUTSON 20 2007-12-03 11:26:44.263 Consultant 2002-08-26 00:00:00.000 MN 763/424-1880 126970 999999999-6 COMPRESSED ACCOUNT 2005-01-01 00:00:00.000 0 1899-12-30 00:00:00.000
0010414 LAURA A KNUTSON 50 2007-12-04 13:58:43.000 Team Manager 2002-08-26 00:00:00.000 MN 763/424-1880 126970 999999999-6 COMPRESSED ACCOUNT 2005-01-01 00:00:00.000 0 1899-12-30 00:00:00.000
0010416 MELODY D TAYLOR 20 2007-12-03 11:26:44.263 Consultant 2002-08-26 00:00:00.000 MN 320/229-9097 126970 999999999-6 COMPRESSED ACCOUNT 2004-01-01 00:00:00.000 0 1899-12-30 00:00:00.000
0010416 MELODY D TAYLOR 50 2007-12-04 13:58:43.000 Team Manager 2002-08-26 00:00:00.000 MN 320/229-9097 126970 999999999-6 COMPRESSED ACCOUNT 2004-01-01 00:00:00.000 0 1899-12-30 00:00:00.000
0010428 JESSICA BURNS 20 2007-12-03 11:26:44.263 Consultant 2002-08-26 00:00:00.000 MN 320.839.0524 126970 999999999-6 COMPRESSED ACCOUNT 2003-04-01 00:00:00.000 0 1899-12-30 00:00:00.000
0010428 JESSICA BURNS 50 2007-12-04 13:58:43.000 Team Manager 2002-08-26 00:00:00.000 MN 320.839.0524 126970 999999999-6 COMPRESSED ACCOUNT 2003-04-01 00:00:00.000 0 1899-12-30 00:00:00.000
0010450 KITTY CHOATE 20 2007-12-03 11:26:44.263 Consultant 2002-08-27 00:00:00.000 MN 763/498-6700 126970 999999999-6 COMPRESSED ACCOUNT 2005-01-01 00:00:00.000 0 2004-11-17 00:00:00.000
0010450 KITTY CHOATE 50 2007-12-04 13:58:43.000 Team Manager 2002-08-27 00:00:00.000 MN 763/498-6700 126970 999999999-6 COMPRESSED ACCOUNT 2005-01-01 00:00:00.000 0 2004-11-17 00:00:00.000
0010455 AMY SUE HANSON 20 2007-12-03 11:26:44.263 Consultant 2002-08-27 00:00:00.000 MN 763.566.0447 126970 999999999-6 COMPRESSED ACCOUNT 2005-07-01 00:00:00.000 0 1899-12-30 00:00:00.000
0010455 AMY SUE HANSON 50 2007-12-04 13:58:43.000 Team Manager 2002-08-27 00:00:00.000 MN 763.566.0447 126970 999999999-6 COMPRESSED ACCOUNT 2005-07-01 00:00:00.000 0 1899-12-30 00:00:00.000
0010456 Sheila Steinmetz 20 2008-02-04 00:00:00.000 Consultant 2002-08-27 00:00:00.000 MN 651.459.0442 63265 0000112 SHERI FRIEDERICHS NULL 1 1899-12-30 00:00:00.000
0010456 Sheila Steinmetz 20 2008-02-15 00:00:00.000 Consultant 2002-08-27 00:00:00.000 MN 651.459.0442 63265 0000112 SHERI FRIEDERICHS 2008-02-15 00:00:00.000 0 NULL
0010456 Sheila Steinmetz 20 2008-03-15 00:00:00.000 Consultant 2002-08-27 00:00:00.000 MN 651.459.0442 63265 0000112 SHERI FRIEDERICHS 2008-02-15 00:00:00.000 1 2008-03-15 00:00:00.000
0010456 Sheila Steinmetz 20 2008-03-31 00:00:00.000 Consultant 2002-08-27 00:00:00.000 MN 651.459.0442 63265 0000112 SHERI FRIEDERICHS 2008-03-31 00:00:00.000 0 2008-03-15 00:00:00.000
0010456 Sheila Steinmetz 20 2008-04-15 00:00:00.000 Consultant 2002-08-27 00:00:00.000 MN 651.459.0442 63265 0000112 SHERI FRIEDERICHS 2008-03-31 00:00:00.000 1 2008-04-15 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 17, 2008 at 7:23 am
What if you get rid of the DISTINCT as it is redundant?
Also shouldn't the group by match the fields, so change the group by c fields to the same value used in the select.
April 17, 2008 at 7:23 am
An aggregate with a group by will return the 1 value for each unique set of columns as per he group by.
In your case, it will return the max effective date per consultantID, consultantName, ...
What it is you want? The max date along with the values of other columns for that particular row?
If so, this is more the construct you want (not tested and probabaly not 100% correct. Should give you the idea)
SELECT < Column list > FROM
#Temp t
Left Outer Join Consultant c ON t.SponsorXID = c.ConsultantXID
LEFT Outer JOin sharedDimension..DimConsultantTitle s ON CurrentLevelXID = s.XID
WHERE (t.BillToState = @State
OR (@State IS NULL))
AND t.EffectiveDate = (SELECT Max(EffectiveDate) FROM Consultant))
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2008 at 7:39 am
Just to clarify, Because I am unclear as to his original intent.
Your suggestion will return only consultants with a record that has the one selected max effective date.
My suggested tweaks should produce a record per consultant with their max effective date.
SELECT t.ConsultantID
, t.ConsultantName
, t.CurrentLevelXID
, MAX(t.EffectiveDate) AS EffectiveDate
, s.Title
, t.NACDate
, t.BillToState
, t.HomePhone
, t.SponsorXID
, t.SponsorID
, c.FirstName + ' ' + c.LastName AS SponsorName
, t.DeactivationDate
, t.Active
, t.ReactivateDt
FROM #Temp t
LEFT JOIN Consultant c
ON t.SponsorXID = c.ConsultantXID
LEFT JOIN sharedDimension..DimConsultantTitle s
ON t.CurrentLevelXID = s.XID
WHERE t.BillToState = @State
OR (@State IS NULL)
GROUP BY t.ConsultantID
, t.ConsultantName
, t.CurrentLevelXID
, s.Title
, T.NACDate
, t.BillToState
, t.HomePhone
, t.SponsorXID
, t.DeactivationDate
, t.Active
, T.SponsorID
, t.ReactivateDt
, c.FirstName + ' ' + c.LastName
April 17, 2008 at 7:46 am
It looks like you have multiple titles for your consultants in the:
sharedDimension..DimConsultantTitle s ON t.CurrentLevelXID = s.XID
Is it possible for your consultants to have multiple active Job Titles?
or is there another field in DimConsultantTitle that will clarify the current active title?
Dave Novak
April 17, 2008 at 8:01 am
This is the code that seems to make it work appropriately:
Select DISTINCT t.ConsultantID,
t.ConsultantName,
t.CurrentLevelXID,
MAX(t.EffectiveDate) AS EffectiveDate,
s.Title,
t.NACDate,
t.BillToState,
t.HomePhone,
t.SponsorXID,
t.SponsorID,
c.FirstName + ' ' + c.LastName AS SponsorName,
t.DeactivationDate,
t.Active
,t.ReactivateDt
from #Temp3 t
Left Outer Join Consultant c ON t.SponsorXID = c.ConsultantXID
LEFT Outer JOin sharedDimension..DimConsultantTitle s ON t.CurrentLevelXID = s.XID
WHERE t.EffectiveDate IN (Select Max(EffectiveDate) from #Temp3
GROUP BY ConsultantID) AND
t.BillToState = @State
OR (@State IS NULL)GROUP BY
t.ConsultantID
,t.ConsultantName
--,c.EffectiveDate
,t.CurrentLevelXID
,s.Title
,T.NACDate
,t.BillToState
,t.HomePhone
,t.SponsorXID
,t.DeactivationDate
,t.Active
,T.SponsorID
,t.ReactivateDt
,c.FirstName
,c.LastName
Drop Table #Temp3
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply