Narrow down the recordset

Question

Narrow down the recordset

alorenzini

SSCarpal Tunnel

Points: 4620
More actions
February 8, 2008 at 7:40 am

#182476

I have the following recordset being returned and I need to narrow it down even further:
ConsultantID Level Title PeriodEndDate
0002617 02 Consultant 2001-08-31
0002617 04 Team Leader 2002-03-31
0002617 05 Team Manager 2002-06-30
0002617 06 Senior Team Manager2002-09-30
0002617 03 Senior Consultant2007-08-31
0002617 04 Team Leader 2007-09-30
0002617 05 Team Manager 2008-01-31
What I am after is a history or repromotions, so in this case this consultant has repromoted twice so the new recordset would look like this:
Team Leader 2007-09-30
Team Manager 2008-01-31
Any ideas?
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 15 (of 124 total)

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

You could use the row_number() function, so in this case you would number the promotions for a particular person to a particular level, and ignore the first promotion to this level by requiring this number to be greater than one (i.e. the second, third, ... promotion)

SELECT ConsultantID

, Level

, Title

, PeriodEndDate

FROM ( SELECT row_number() OVER ( PARTITION BY Level ORDER BY PeriodEndDate ) AS Nr

, ConsultantID

, Level

, Title

, PeriodEndDate

FROM ... the original query

) AS x

WHERE x.Nr > 1

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 2

I adapted your query and it looks like this:

SELECT ConsultantID

, Level

, Title

, PeriodEndDate

FROM

( SELECT

row_number() OVER ( PARTITION BY Level ORDER BY PeriodEndDate ) AS Nr

, ConsultantID

, Level

, Title

, PeriodEndDate

FROM (Select ConsultantID,

AchieveLevel,

AchieveTitle,

PeriodEndDate from #Temp

WHERE PaidLevel <> AchieveLevel ) AS x

WHERE x.Nr > 1

But I get the following syntax error:

Msg 102, Level 15, State 1, Line 17

Incorrect syntax near '1'.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

alorenzini (2/8/2008)
I adapted your query and it looks like this:
SELECT ConsultantID
, Level
, Title
, PeriodEndDate
FROM
( SELECT
row_number() OVER ( PARTITION BY Level ORDER BY PeriodEndDate ) AS Nr
, ConsultantID
, Level
, Title
, PeriodEndDate
FROM (Select ConsultantID,
AchieveLevel,
AchieveTitle,
PeriodEndDate from #Temp
WHERE PaidLevel <> AchieveLevel ) AS x
WHERE x.Nr > 1
But I get the following syntax error:
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '1'.

You seem to have missed a closing parantheses:

SELECT ConsultantID

, Level

, Title

, PeriodEndDate

FROM ( SELECT row_number() OVER ( PARTITION BY Level ORDER BY PeriodEndDate ) AS Nr

, ConsultantID

, Level

, Title

, PeriodEndDate

FROM ( SELECT ConsultantID

, AchieveLevel

, AchieveTitle

, PeriodEndDate

FROM #Temp

WHERE PaidLevel <> AchieveLevel

)

) AS x

WHERE x.Nr > 1

Also, in this query (unlike in your original one), the ConsultantID could be different. You may want to add this column to the partition by column in the order clause.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Adam Haines SSC-Insane Points: 23217 More actions · Answer 4

This query will yeild the same results

SELECT DISTINCT b.ConsultantID, b.Level, b.Title, b.PeriodEndDate

FROM @C a INNER JOIN @C b ON a.ConsultantID = b.ConsultantID

AND a.Level = b.Level AND a.PeriodEndDate < b.PeriodEndDate

ORDER BY b.PeriodEndDate

EDITED: To fix missed code tag

My blog: http://jahaines.blogspot.com

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 5

SELECT ConsultantID

, Level

, Title

, PeriodEndDate

FROM

( SELECT row_number() OVER ( PARTITION BY AchieveLevel ORDER BY PeriodEndDate ) AS Nr

, ConsultantID

, AchieveLevel

, AchieveTitle

, PeriodEndDate

FROM ( SELECT ConsultantID

, AchieveLevel

, AchieveTitle

, PeriodEndDate

FROM #Temp

WHERE PaidLevel <> AchieveLevel)

) AS x

WHERE x.Nr > 1

I am now getting the following error, does it have to do with AS x in the WHere clause?

Msg 102, Level 15, State 1, Line 18

Incorrect syntax near ')'.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

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

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 6

SELECT DISTINCT b.ConsultantID,

b.AchieveLevel,

b.AchieveTitle,

b.PeriodEndDate

FROM #Temp a

INNER JOIN #Temp b ON a.ConsultantID = b.ConsultantID

AND a.PaidLevel =b.AchieveLevel AND a.PeriodEndDate < b.PeriodEndDate

ORDER BY b.PeriodEndDate

This returns the entire recordset not the two records I am looking for..

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 7

You have something wrong, take a look my test data:

DECLARE @C TABLE(

ConsultantID CHAR(7),

Level CHAR(2),

Title CHAR(25),

PeriodEndDate DATETIME

)

INSERT INTO @C

SELECT '0002617', '02', 'Consultant', '2001-08-31' UNION ALL

SELECT '0002617', '04', 'Team Leader', '2002-03-31' UNION ALL

SELECT '0002617', '05', 'Team Manager', '2002-06-30' UNION ALL

SELECT '0002617', '06', 'Senior Team Manager', '2002-09-30' UNION ALL

SELECT '0002617', '03', 'Senior Consultant', '2007-08-31' UNION ALL

SELECT '0002617', '04', 'Team Leader', '2007-09-30' UNION ALL

SELECT '0002617', '05', 'Team Manager', '2008-01-31'

--Team Leader 2007-09-30

--Team Manager 2008-01-31

SELECT DISTINCT b.ConsultantID, b.Level, b.Title, b.PeriodEndDate

FROM @C a INNER JOIN @C b ON a.ConsultantID = b.ConsultantID

AND a.Level = b.Level AND a.PeriodEndDate < b.PeriodEndDate

ORDER BY b.PeriodEndDate

My blog: http://jahaines.blogspot.com

Adam Haines SSC-Insane Points: 23217 More actions · Answer 8

SELECT DISTINCT b.ConsultantID,
b.AchieveLevel,
b.AchieveTitle,
b.PeriodEndDate
FROM #Temp a
INNER JOIN #Temp b ON a.ConsultantID = b.ConsultantID
AND a.PaidLevel =b.AchieveLevel AND a.PeriodEndDate < b.PeriodEndDate
ORDER BY b.PeriodEndDate
This returns the entire recordset not the two records I am looking for..

The query works off the ddl you posted that there is only one "Level" column. To get this to work you have to set a.level = b.level.

My blog: http://jahaines.blogspot.com

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 9

This almost works but not quite, here is another record set:

COnsultantID Level Title PeriodEndDate

0002354 01 NAC Consultant02/28/2001

0002354 02 Consultant 03/31/2001

0002354 02 Consultant 04/30/2001

0002354 02 Consultant 05/31/2001

0002354 02 Consultant 06/30/2001

0002354 02 Consultant 07/31/2001

0002354 02 Consultant 08/31/2001

0002354 02 Consultant 09/30/2001

0002354 02 Consultant 10/31/2001

0002354 02 Consultant 11/30/2001

0002354 04 Team Leader 12/31/2001

0002354 04 Team Leader 01/31/2002

0002354 04 Team Leader 02/28/2002

0002354 04 Team Leader 03/31/2002

0002354 04 Team Leader 04/30/2002

0002354 04 Team Leader 05/31/2002

0002354 04 Team Leader 06/30/2002

0002354 04 Team Leader 07/31/2002

0002354 04 Team Leader 08/31/2002

0002354 04 Team Leader 09/30/2002

0002354 04 Team Leader 10/31/2002

0002354 04 Team Leader 11/30/2002

0002354 05 Team Manager 12/31/2002

0002354 05 Team Manager 01/31/2003

0002354 05 Team Manager 02/28/2003

0002354 05 Team Manager 03/31/2003

0002354 05 Team Manager 04/30/2003

0002354 04 Team Leader 05/31/2003

0002354 04 Team Leader 06/30/2003

0002354 04 Team Leader 07/31/2003

0002354 04 Team Leader 08/31/2003

0002354 05 Team Manager 09/30/2003

0002354 05 Team Manager 10/31/2003

0002354 05 Team Manager 11/30/2003

0002354 05 Team Manager 12/31/2003

0002354 05 Team Manager 01/31/2004

0002354 04 Team Leader 02/29/2004

0002354 04 Team Leader 03/31/2004

0002354 04 Team Leader 04/30/2004

0002354 04 Team Leader 05/31/2004

0002354 04 Team Leader 06/30/2004

0002354 04 Team Leader 07/31/2004

0002354 04 Team Leader 08/31/2004

0002354 04 Team Leader 09/30/2004

0002354 05 Team Manager 10/31/2004

0002354 05 Team Manager 11/30/2004

0002354 05 Team Manager 12/31/2004

0002354 05 Team Manager 01/31/2005

0002354 05 Team Manager 02/28/2005

0002354 05 Team Manager 03/31/2005

0002354 03 Senior Consultant04/30/2005

0002354 03 Senior Consultant05/31/2005

0002354 03 Senior Consultant06/30/2005

0002354 03 Senior Consultant07/31/2005

0002354 03 Senior Consultant08/31/2005

0002354 03 Senior Consultant09/30/2005

0002354 03 Senior Consultant10/31/2005

0002354 03 Senior Consultant11/30/2005

0002354 03 Senior Consultant12/31/2005

0002354 04 Team Leader 01/31/2006

0002354 04 Team Leader 02/28/2006

0002354 04 Team Leader 03/31/2006

0002354 03 Senior Consultant04/30/2006

0002354 03 Senior Consultant05/31/2006

0002354 03 Senior Consultant06/30/2006

0002354 03 Senior Consultant07/31/2006

0002354 03 Senior Consultant08/31/2006

0002354 03 Senior Consultant09/30/2006

0002354 03 Senior Consultant10/31/2006

0002354 03 Senior Consultant11/30/2006

0002354 03 Senior Consultant12/31/2006

0002354 03 Senior Consultant01/31/2007

0002354 03 Senior Consultant02/28/2007

0002354 03 Senior Consultant03/31/2007

0002354 03 Senior Consultant04/30/2007

0002354 03 Senior Consultant05/31/2007

0002354 03 Senior Consultant06/30/2007

0002354 03 Senior Consultant07/31/2007

0002354 03 Senior Consultant08/31/2007

0002354 03 Senior Consultant09/30/2007

0002354 03 Senior Consultant10/31/2007

0002354 03 Senior Consultant11/30/2007

0002354 03 Senior Consultant12/31/2007

0002354 03 Senior Consultant01/31/2008

In this case, the recordset should return

0002354 05 Team Manager

0002354 04 Team Leader

0002354 03 Senior Consultant

Because these were the repromotes. Instead it is just returning two records for Team Leader. THis is the code as it sits now:

SELECT * INTO #Temp from volume a

where exists (select * from volume b

where Repflag='D' and

b.consultantid=a.consultantid

--and b.periodEndDate<a.periodenddate

and a.AchieveLevel<>b.achieveLevel AND ConsultantID = '0002354')

Select ConsultantID, AchieveLevel, AchieveTitle, PeriodEndDate into #Temp2 from #Temp a

WHERE PaidLevel <> AchieveLevel

SELECT DISTINCT b.ConsultantID, b.AchieveLevel, b.AchieveTitle, b.PeriodEndDate

FROM #Temp2 a INNER JOIN #Temp2 b ON a.ConsultantID = b.ConsultantID

AND a.AchieveLevel = b.AchieveLevel AND a.PeriodEndDate < b.PeriodEndDate

ORDER BY b.PeriodEndDate

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

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

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 10

THis might help give you an idea of the records that needed to be pulled out (see attachment) the high lighted rows are the rows that need to be returned:

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 11

Alorenzini,

I tried to avoid using a type of cursor for this query but I could not avoid it. Maybe some of the others guys will have some better methods. This query will return the data you are looking for.

DECLARE @C TABLE(

Row_ID INT IDENTITY(1,1),

ConsultantID CHAR(7),

Level CHAR(2),

Title CHAR(25),

PeriodEndDate DATETIME

)

INSERT INTO @C

SELECT '0002354', '01' ,'NAC Consultant', '02/28/2001' UNION ALL

SELECT '0002354', '02' ,'Consultant', '03/31/2001' UNION ALL

SELECT '0002354', '02' , 'Consultant', '04/30/2001' UNION ALL

SELECT '0002354', '02' , 'Consultant', '05/31/2001' UNION ALL

SELECT '0002354', '02' , 'Consultant', '06/30/2001' UNION ALL

SELECT '0002354', '02' , 'Consultant', '07/31/2001' UNION ALL

SELECT '0002354', '02' , 'Consultant', '08/31/2001' UNION ALL

SELECT '0002354', '02' , 'Consultant', '09/30/2001' UNION ALL

SELECT '0002354', '02' , 'Consultant', '10/31/2001' UNION ALL

SELECT '0002354', '02' , 'Consultant', '11/30/2001' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '12/31/2001' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '01/31/2002' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '02/28/2002' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '03/31/2002' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '04/30/2002' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '05/31/2002' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '06/30/2002' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '07/31/2002' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '08/31/2002' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '09/30/2002' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '10/31/2002' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '11/30/2002' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '12/31/2002' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '01/31/2003' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '02/28/2003' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '03/31/2003' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '04/30/2003' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '05/31/2003' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '06/30/2003' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '07/31/2003' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '08/31/2003' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '09/30/2003' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '10/31/2003' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '11/30/2003' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '12/31/2003' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '01/31/2004' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '02/29/2004' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '03/31/2004' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '04/30/2004' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '05/31/2004' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '06/30/2004' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '07/31/2004' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '08/31/2004' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '09/30/2004' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '10/31/2004' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '11/30/2004' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '12/31/2004' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '01/31/2005' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '02/28/2005' UNION ALL

SELECT '0002354', '05' , 'Team Manager', '03/31/2005' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '04/30/2005' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '05/31/2005' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '06/30/2005' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '07/31/2005' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '08/31/2005' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '09/30/2005' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '10/31/2005' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '11/30/2005' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '12/31/2005' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '01/31/2006' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '02/28/2006' UNION ALL

SELECT '0002354', '04' , 'Team Leader', '03/31/2006' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '04/30/2006' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '05/31/2006' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '06/30/2006' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '07/31/2006' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '08/31/2006' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '09/30/2006' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '10/31/2006' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '11/30/2006' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '12/31/2006' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '01/31/2007' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '02/28/2007' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '03/31/2007' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '04/30/2007' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '05/31/2007' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '06/30/2007' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '07/31/2007' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '08/31/2007' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '09/30/2007' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '10/31/2007' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '11/30/2007' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '12/31/2007' UNION ALL

SELECT '0002354', '03' , 'Senior Consultant', '01/31/2008'

DECLARE @counter INT,

@prev CHAR(2),

@NbrRows INT

SET @counter = 1

SET @prev = NULL

SET @NbrRows = (SELECT COUNT(*) FROM @C)

DECLARE @r TABLE(

ConsultantID CHAR(7),

Level CHAR(2),

Title CHAR(25),

PeriodEndDate DATETIME

)

WHILE @counter <= @NbrRows

BEGIN

INSERT INTO @r

SELECT a.ConsultantID, a.Level, a.Title,a.PeriodEndDate

FROM @C a

WHERE a.Row_Id = @counter AND a.Level > @prev AND a.Level IN

(SELECT b.Level

FROM @C b

WHERE b.Row_ID BETWEEN 1 AND @counter - 1)

SELECT @prev = a.Level

FROM @C a

WHERE a.Row_ID = @counter

SET @counter = @counter + 1

END

SELECT *

FROM @r

My blog: http://jahaines.blogspot.com

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

Art - this is different from what we tried to tackle in December?

I'm talking about this thread:

http://www.sqlservercentral.com/Forums/Topic431378-338-2.aspx

Just curious. If you can get a full list of the "rules" - we can try to figure out what no longer works from the other one.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 13

And, maybe learn how to post sample data to get better answers...

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

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Using Adam's data

WITH Promotions AS (

SELECT a.ConsultantID,

a.Level,

a.Title,

a.PeriodEndDate,

ROW_NUMBER() OVER(PARTITION BY a.ConsultantID, a.Level ORDER BY a.PeriodEndDate) as rn

FROM @C a

WHERE EXISTS (SELECT * FROM @C b

WHERE b.ConsultantID=a.ConsultantID

AND b.Level<a.Level

AND b.PeriodEndDate<a.PeriodEndDate

AND NOT EXISTS (SELECT * FROM @C c

WHERE c.ConsultantID=a.ConsultantID

AND c.PeriodEndDate>b.PeriodEndDate

AND c.PeriodEndDate<a.PeriodEndDate))

)

SELECT ConsultantID,

Level,

Title,

PeriodEndDate

FROM Promotions

WHERE rn>1

ORDER BY 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