Using WITH CTE

Question

Using WITH CTE

alorenzini

SSCarpal Tunnel

Points: 4620
More actions
December 19, 2007 at 12:53 pm

#184841

This Code:
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 AchieveLevel, PeriodEnddate FROM Alevel
Gives me the following results:
AchieveLevel PeriodEndDate
01 2001-05-31 00:00:00.000
02 2001-06-30 00:00:00.000
02 2001-07-31 00:00:00.000
02 2001-08-31 00:00:00.000
02 2001-09-30 00:00:00.000
02 2001-10-31 00:00:00.000
02 2001-11-30 00:00:00.000
02 2001-12-31 00:00:00.000
02 2002-01-31 00:00:00.000
02 2002-02-28 00:00:00.000
02 2002-03-31 00:00:00.000
02 2002-04-30 00:00:00.000
04 2002-05-31 00:00:00.000
04 2002-06-30 00:00:00.000
04 2002-07-31 00:00:00.000
04 2002-08-31 00:00:00.000
04 2002-09-30 00:00:00.000
05 2002-10-31 00:00:00.000
05 2002-11-30 00:00:00.000
05 2002-12-31 00:00:00.000
05 2003-01-31 00:00:00.000
05 2003-02-28 00:00:00.000
06 2003-03-31 00:00:00.000
06 2003-04-30 00:00:00.000
06 2003-05-31 00:00:00.000
06 2003-06-30 00:00:00.000
06 2003-07-31 00:00:00.000
06 2003-08-31 00:00:00.000
06 2003-09-30 00:00:00.000
07 2003-10-31 00:00:00.000
07 2003-11-30 00:00:00.000
07 2003-12-31 00:00:00.000
07 2004-01-31 00:00:00.000
07 2004-02-29 00:00:00.000
07 2004-03-31 00:00:00.000
07 2004-04-30 00:00:00.000
07 2004-05-31 00:00:00.000
07 2004-06-30 00:00:00.000
07 2004-07-31 00:00:00.000
07 2004-08-31 00:00:00.000
07 2004-09-30 00:00:00.000
07 2004-10-31 00:00:00.000
07 2004-11-30 00:00:00.000
07 2004-12-31 00:00:00.000
07 2005-01-31 00:00:00.000
07 2005-02-28 00:00:00.000
07 2005-03-31 00:00:00.000
06 2005-04-30 00:00:00.000
06 2005-05-31 00:00:00.000
06 2005-06-30 00:00:00.000
06 2005-07-31 00:00:00.000
06 2005-08-31 00:00:00.000
07 2005-09-30 00:00:00.000
07 2005-10-31 00:00:00.000
07 2005-11-30 00:00:00.000
07 2005-12-31 00:00:00.000
07 2006-01-31 00:00:00.000
07 2006-02-28 00:00:00.000
07 2006-03-31 00:00:00.000
07 2006-04-30 00:00:00.000
07 2006-05-31 00:00:00.000
07 2006-06-30 00:00:00.000
07 2006-07-31 00:00:00.000
07 2006-08-31 00:00:00.000
07 2006-09-30 00:00:00.000
07 2006-10-31 00:00:00.000
07 2006-11-30 00:00:00.000
07 2006-12-31 00:00:00.000
07 2007-01-31 00:00:00.000
07 2007-02-28 00:00:00.000
07 2007-03-31 00:00:00.000
07 2007-04-30 00:00:00.000
07 2007-05-31 00:00:00.000
07 2007-06-30 00:00:00.000
07 2007-07-31 00:00:00.000
07 2007-08-31 00:00:00.000
07 2007-09-30 00:00:00.000
04 2007-10-31 00:00:00.000
07 2007-11-30 00:00:00.000
But the results that I am after is:
01 2001-05-31 00:00:00.000
02 2002-04-30 00:00:00.000
04 2002-09-30 00:00:00.000
05 2003-02-28 00:00:00.000
06 2003-09-30 00:00:00.000
07 2005-03-31 00:00:00.000
06 2005-08-31 00:00:00.000
07 2007-09-30 00:00:00.000
04 2007-10-31 00:00:00.000
07 2007-11-30 00:00:00.000
Basically I need the max periodenddate for each AchieveLevel. Soes ths make sense?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

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

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

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

alorenzini (12/19/2007)
Basically I need the max periodenddate for each AchieveLevel. Soes ths make sense?

It would, if level 07 wasn't in your desired output 3 times. The max period date is easy. Max(periodDate)...group by AchieveLevel

From the output you gave though, I'm guessing you want something a bit more complex?

Edit: Is this related to your other active thread "A single row per"?

If so, it's better not to cross post, as it fragments posts, and wastes people's time. The regulars here read all the forums.

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

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 2

That's the issue. I can do it if I just wanted distinct AchieveLevels but that is not the case. I need one record for each change in AchieveLevel.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

I think this can be simplified, but should work

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

) ,

CTE AS (

SELECT AchieveLevel, PeriodEnddate,

ROW_NUMBER() OVER(PARTITION BY AchieveLevel ORDER BY PeriodEnddate)-

ROW_NUMBER() OVER(ORDER BY PeriodEnddate) as rn

FROM Alevel)

SELECT AchieveLevel,MAX(PeriodEnddate) AS PeriodEnddate

FROM CTE

GROUP BY AchieveLevel,rn

ORDER BY MAX(PeriodEnddate)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 4

That's the ticket. Thanks.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!