April 25, 2008 at 10:27 am
Issue is I have multiple columns being returned where I just need the latest. I have tried to use MAX to get to the most recent record but I am still getting two records back.
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @status nvarchar(100)
DECLARE @State int
DECLARE @DateType Nvarchar(100)
SET @StartDate = '09/01/2007'
SET @EndDate = '09/30/2007'
SET @status = 'All'
SET @State = 0
SET @DateType ='NAC Date'
IF @status = 'All'
BEGIN
SET @status=NULL
END
IF @DateType = 'NAC DATE'
BEGIN
Select c.ConsultantID
,c.LastName + ', ' + c.FirstName as ConsultantName
,MAX(c.EffectiveDate) AS EffectiveDate
,c.CurrentLevelXID
,c.BillToRegionXID
,c.BillToState
,c.HomePhone
,c.SponsorXID
,c.SponsorID
,c.NACDate
,c.DeactivationDate
,c.Active
,c.ReactivateDt
FROM Consultant c
WHERE
c.NACDate >= @StartDate AND c.NACDate <=@EndDATE
AND c.Active = @status OR (@Status IS NULL AND c.NACDate >= @StartDate AND c.NACDate <=@EndDATE)
GROUP BY c.ConsultantID,c.LAstName, c.FirstName,c.CurrentLevelXID
,c.BillToRegionXID
,c.BillToState
,c.HomePhone
,c.SponsorXID
,c.SponsorID
,c.NACDate
,c.DeactivationDate
,c.Active
,c.ReactivateDt
ORDER BY 2
END
Result set:
ConsultantID ConsultantName EffectiveDate CurrentLevelXID NACDate
------------- ------------------------------ ----------------------- --------------- -----------------------
0068076 Abbott, Erika 2007-12-04 13:58:43.000 20 2007-09-04 00:00:00.000
0068076 Abbott, Erika 2007-12-03 11:26:44.263 10 2007-09-04 00:00:00.0000069039 Ackerman, Sara 2007-12-04 13:58:43.000 50 2007-09-24 00:00:00.000
0069039 Ackerman, Sara 1995-01-01 00:00:00.000 10 2007-09-24 00:00:00.000
0069438 Adams, Shelley 1995-01-01 00:00:00.000 10 2007-09-28 00:00:00.000
0069438 Adams, Shelley 2007-12-04 13:58:43.000 50 2007-09-28 00:00:00.000
0069296 Addis, Kim 1995-01-01 00:00:00.000 20 2007-09-27 00:00:00.000
0069296 Addis, Kim 2007-12-04 13:58:43.000 50 2007-09-27 00:00:00.000
0068823 Adkins, Kristi 2007-12-04 13:58:43.000 50 2007-09-20 00:00:00.000
0068639 Aguilar, Kim 2007-12-03 11:26:44.263 10 2007-09-17 00:00:00.000
0068639 Aguilar, Kim 2007-12-04 13:58:43.000 50 2007-09-17 00:00:00.000
0068036 Ahrens, Yvonne 2007-12-04 13:58:43.000 50 2007-09-04 00:00:00.000
0068036 Ahrens, Yvonne 2007-12-03 11:26:44.263 20 2007-09-04 00:00:00.000
0068501 Aiello, Geraldine 2007-12-04 13:58:43.000 50 2007-09-14 00:00:00.000
0068501 Aiello, Geraldine 2007-12-03 11:26:44.263 10 2007-09-14 00:00:00.000
So in this case, I would want the record from 2007-12-03 11:26:44.263 for the first consultant.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 25, 2008 at 10:33 am
It looks to me like you have different values in CurrentLevelXID column, and you are grouping by that column.
Instead of grouping by all the columns, you should use a derived table (or CTE) to determine the last date for each ConsultantID, then join to that using the ConsultantID and the date.
Something like this:
;with LastDate as
(select consultantid as CID, max(effectivedate) as LDate
from consultant)
Select c.ConsultantID
,c.LastName + ', ' + c.FirstName as ConsultantName
,c.EffectiveDate
,c.CurrentLevelXID
,c.BillToRegionXID
,c.BillToState
,c.HomePhone
,c.SponsorXID
,c.SponsorID
,c.NACDate
,c.DeactivationDate
,c.Active
,c.ReactivateDt
FROM Consultant c
inner join LastDate
on consultantid = cid
and effectivedate = ldate
WHERE
c.NACDate >= @StartDate AND c.NACDate <=@EndDATE
AND c.Active = @status OR
(@Status IS NULL AND c.NACDate >= @StartDate AND c.NACDate <=@EndDATE)
ORDER BY 2
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 25, 2008 at 2:52 pm
THis worked great. Thanks a lot.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply