December 13, 2007 at 12:05 pm
I have the following code:
Select DistinctAchieveTitle
,PeriodEndDate
from Volume where ConsultantID = '0000023'
What this does is return the following:
Team Manager 2000-07-31 00:00:00.000
Team Manager 2000-08-31 00:00:00.000
Team Manager 2000-09-30 00:00:00.000
Team Manager 2000-10-31 00:00:00.000
Team Manager 2000-11-30 00:00:00.000
Team Manager 2000-12-31 00:00:00.000
Team Manager 2001-01-31 00:00:00.000
Team Manager 2001-02-28 00:00:00.000
Team Manager 2001-03-31 00:00:00.000
Team Manager 2001-04-30 00:00:00.000
Team Manager 2001-05-31 00:00:00.000
Team Manager 2001-06-30 00:00:00.000
Team Manager 2001-07-31 00:00:00.000
Team Manager 2001-08-31 00:00:00.000
Team Manager 2001-09-30 00:00:00.000
Team Manager 2001-10-31 00:00:00.000
Team Manager 2001-11-30 00:00:00.000
Team Manager 2001-12-31 00:00:00.000
Team Manager 2002-01-31 00:00:00.000
Team Manager 2002-02-28 00:00:00.000
Team Manager 2002-03-31 00:00:00.000
Sr Team Mgr 2002-04-30 00:00:00.000
Sr Team Mgr 2002-05-31 00:00:00.000
Sr Team Mgr 2002-06-30 00:00:00.000
Sr Team Mgr 2002-07-31 00:00:00.000
Sr Team Mgr 2002-08-31 00:00:00.000
Sr Team Manager2002-09-30 00:00:00.000
Team Manager 2002-10-31 00:00:00.000
Team Manager 2002-11-30 00:00:00.000
Team Manager 2002-12-31 00:00:00.000
Team Manager 2003-01-31 00:00:00.000
Sr Team Manager2003-02-28 00:00:00.000
But what I need it return is the following:
Team Manager 2000-07-31 00:00:00.000
Sr Team Mgr 2002-04-30 00:00:00.000
Sr Team Manager2002-09-30 00:00:00.000
Team Manager 2002-10-31 00:00:00.000
Sr Team Manager2003-02-28 00:00:00.000
Any thoughts for this grasshopper?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 13, 2007 at 12:18 pm
select achievetitle, max(periodenddate)
Use that instead of "distinct".
- 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
December 13, 2007 at 12:41 pm
This is really close:
select achievetitle,achieveLevel, max(periodenddate)
from Volume where ConsultantID = '0000288'
GROUP BY AchieveTitle, AChievelevel
But it needs to appear in PeriodEndate order but when I put a group by on with an order clause I go to my orginal problem.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 13, 2007 at 1:40 pm
Here's what I did, hope it helps...
create table #test (a varchar(1000), b datetime)
insert into #test values ('Team Manager', '2000-08-31 00:00:00.000')
insert into #test values ('Team Manager', '2000-09-30 00:00:00.000')
insert into #test values ('Team Manager', '2000-10-31 00:00:00.000')
insert into #test values ('Team Manager', '2000-11-30 00:00:00.000')
insert into #test values ('Team Manager', '2000-12-31 00:00:00.000')
insert into #test values ('Team Manager', '2001-01-31 00:00:00.000')
insert into #test values ('Team Manager', '2001-02-28 00:00:00.000')
insert into #test values ('Team Manager', '2001-03-31 00:00:00.000')
insert into #test values ('Team Manager', '2001-04-30 00:00:00.000')
insert into #test values ('Team Manager', '2001-05-31 00:00:00.000')
insert into #test values ('Team Manager', '2001-06-30 00:00:00.000')
insert into #test values ('Team Manager', '2001-07-31 00:00:00.000')
insert into #test values ('Team Manager', '2001-08-31 00:00:00.000')
insert into #test values ('Team Manager', '2001-09-30 00:00:00.000')
insert into #test values ('Team Manager', '2001-10-31 00:00:00.000')
insert into #test values ('Team Manager', '2001-11-30 00:00:00.000')
insert into #test values ('Team Manager', '2001-12-31 00:00:00.000')
insert into #test values ('Team Manager', '2002-01-31 00:00:00.000')
insert into #test values ('Team Manager', '2002-02-28 00:00:00.000')
insert into #test values ('Team Manager', '2002-03-31 00:00:00.000')
insert into #test values ('Sr Team Mgr', '2002-04-30 00:00:00.000')
insert into #test values ('Sr Team Mgr', '2002-05-31 00:00:00.000')
insert into #test values ('Sr Team Mgr', '2002-06-30 00:00:00.000')
insert into #test values ('Sr Team Mgr', '2002-07-31 00:00:00.000')
insert into #test values ('Sr Team Mgr', '2002-08-31 00:00:00.000')
insert into #test values ('Sr Team Manager', '2002-09-30 00:00:00.000')
insert into #test values ('Team Manager', '2002-10-31 00:00:00.000')
insert into #test values ('Team Manager', '2002-11-30 00:00:00.000')
insert into #test values ('Team Manager', '2002-12-31 00:00:00.000')
insert into #test values ('Team Manager', '2003-01-31 00:00:00.000')
insert into #test values ('Sr Team Manager', '2003-02-28 00:00:00.000')
select A, min(B)
from #test
GROUP BY a
order by 1,2
December 13, 2007 at 1:41 pm
You just need to alias it, for instance:
select achievetitle,achieveLevel, max(periodenddate) as Last_periodenddate
from Volume where ConsultantID = '0000288'
GROUP BY AchieveTitle, AChievelevel
order by Last_periodenddate
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 13, 2007 at 1:48 pm
That last one was the ticket. Thanks.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 19, 2007 at 10:20 am
There seems to be something missing:
selectachievetitle
,achieveLevel
,max(periodenddate) AS PeriodEndDate
from Volume
where ConsultantID = @ConsultantID
GROUP BY AchieveTitle
,AChievelevel
order by periodenddate
This returns
NAC Consultant01 2001-05-31 00:00:00.000
Consultant 02 2002-04-30 00:00:00.000
Team Manager 05 2003-02-28 00:00:00.000
Senior Team Manager06 2005-08-31 00:00:00.000
Team Leader 04 2007-10-31 00:00:00.000
Team Mentor 07 2007-11-30 00:00:00.000
which is great as far as it goes. Upon investigation this is not quite right. I need to have a record for each time the AchieveLevel changed.
So in this case, it would be something like this:
NAC Consultant01 2001-05-31 00:00:00.000
Consultant 02 2002-04-30 00:00:00.000
Team Manager 05 2003-02-28 00:00:00.000
Senior Team Manager06 2005-08-31 00:00:00.000
Team Manager 05 2007-08-31 00:00:00:000
Team Leader 04 2007-10-31 00:00:00.000
Team Mentor 07 2007-11-30 00:00:00.000
in this case the person was demoted from a Senior Team Manager to a Team Manager. My current code does not reflect as it shows each distinct AchieveTitle. Any advice?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 19, 2007 at 12:47 pm
Anybody have any thoughts on this? I am really stuck!
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 19, 2007 at 12:54 pm
There's a missing a link... do you have a user id or something that links those 2 records together?
December 19, 2007 at 12:59 pm
The consultantID is the unique identifier.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 19, 2007 at 1:16 pm
it seems that the T-SQL "group by" sorts before grouping which causes that problem... The only way around this (I think) is to use a Cursor (but this can take lots of time and resources)... I would try something else with different Temporary tables to get there... But not sure how...
This little twist makes this a nice little project.
December 19, 2007 at 1:45 pm
The group by doesn't sort at all. A table doesn't have any defined order on it. By definition, it's an unordered set of records
When you say max(periodDate) ... group by achieveLevel, you are asking for the highest value of the period date that each achievelevel in the table has. There's no concept of record order here.
A cursor will work for this. It won't be pretty or fast, but it will work.
The other trick that might work to detect the boundaries of the achievelevels is (what I call) and offset join. Since the dates are all a month apart, it should work.
Something like as a starting point ... (untested)
SELECT *
FROM Volume
LEFT OUTER JOIN Volume NextMonth
ON Volume.periodenddate = dbo.DateMonthEnds(DATEADD(dd,1,NextMonth.periodenddate)
AND Volume.ConsultantID = NextMonth.ConsultantID
WHERE Volumn.AchieveLevel != NextMonth.AchieveLevel OR NextMonth.AchieveLevel IS NULL
I used a function cause it's cleaner. DateMonthEnds as follows (also untested)
CREATE FUNCTION dbo.DateMonthEnds (@theDate DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,@theDate)+1,0))
END
There are probably several better ways to do this.
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
December 19, 2007 at 2:10 pm
That did it.
Final code took this shape I di d use your function.
WITH ALevel (ConsultantID, AchieveLevel, AchieveTitle, PeriodEnddate) AS
(
SELECT ConsultantID, max(AchieveLevel), AchieveTitle, PeriodEndDate AS DirectReports
FROM Volume
WHERE ConsultantID = @ConsultantID
GROUP BY ConsultantID
,AchieveTitle
,PeriodEndDate
)
SELECT a.consultantID,a.AchieveTitle, a.AchieveLevel, a.PeriodEnddate INTO #Alevel FROM Alevel a
SELECT a.ConsultantID,a.AchieveTitle, a.AchieveLevel, a.PeriodEndDate FROM #Alevel a
LEFT OUTER JOIN #Alevel NextMonth
ON a.periodenddate = dbo.DateMonthEnds(DATEADD(dd,1,NextMonth.periodenddate))
AND a.ConsultantID = NextMonth.ConsultantID
WHERE a.AchieveLevel != NextMonth.AchieveLevel OR NextMonth.AchieveLevel IS NULL
Drop table #Alevel
Thank you very much.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 19, 2007 at 2:17 pm
You don't need the temp table. (unless you're using it for performance reasons)
WITH ALevel (ConsultantID, AchieveLevel, AchieveTitle, PeriodEnddate) AS
(
SELECT ConsultantID, max(AchieveLevel), AchieveTitle, PeriodEndDate AS DirectReports
FROM Volume
WHERE ConsultantID = @ConsultantID
GROUP BY ConsultantID
,AchieveTitle
,PeriodEndDate
)
SELECT a.ConsultantID,a.AchieveTitle, a.AchieveLevel, a.PeriodEndDate FROM Alevel a
LEFT OUTER JOIN Alevel NextMonth
ON a.periodenddate = dbo.DateMonthEnds(DATEADD(dd,1,NextMonth.periodenddate))
AND a.ConsultantID = NextMonth.ConsultantID
WHERE a.AchieveLevel != NextMonth.AchieveLevel OR NextMonth.AchieveLevel IS NULL
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
December 19, 2007 at 2:26 pm
Thanks for the tip, I took out the temp table and it runs like a champ.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply