MAX Date question

Question

Post reply

MAX Date question

alorenzini

SSCarpal Tunnel

Points: 4620
More actions
April 17, 2008 at 7:13 am

#181537

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!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WarePhreak SSC Veteran Points: 286 More actions · Answer 1

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

WarePhreak SSC Veteran Points: 286 More actions · Answer 3

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

DAVNovak Hall of Fame Points: 3120 More actions · Answer 4

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

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 5

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!