Query for Second Most Recent Value

[dukeslater]

I'm sure there's a standard, simple technique for this but it's been one of those "beat your head on the desk" kind of days.

Standard table which stores payroll history - empID, rate, changedate:

1001 15.00 1/1/2004
1001 15.50 1/1/2005
1001 16.00 1/1/2006

I need a query to return the current rate and date, and also the previous most recent rate and date:

EmpID CurrentRate CurrentDate PreviousRate PreviousDate
1001 16.00 1/1/2006 15.50 1/1/2005
1002 21.00 12/1/2005 18.50 3/1/2005


I'm hitting all around it by populating a temp table, but can't quite get it right. Thanks for the help!

 

[vongrunt]

Take TOP 2 rows ORDERed descending, then sort 'em ascending.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DotNetGnat]

Try this:

SELECT A.empID, A.rate, A.changeDate
From mytable A WHERE
A.changeDate in (
       Select Top 1 changeDate in From mytable B WHERE
         B.changeDate in (
                      Select Top 2 changeDate From mytable C
                      WHERE B.empID=C.empID AND B.changeDate=C.changeDate order by changeDate ASC)
   WHERE A.empID=B.empID AND A.changeDate=B.changeDate

code not tested...tweak a little bit...
-DNG

 

[vongrunt]

Scrap that...

For each EmpID you want two last Rate/Date values?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[dukeslater]

Tweaking DotNetGnat's code, but ultimately I want one record for each employee that has the current rate/date and the previous date/rate.

Also, some recent employees won't have a previous rate, but nulls are fine for those fields.

Thanks guys...

 

[DotNetGnat]

i missed the closing brace at the end...

try it out...

-DNG

 

[dukeslater]

Query runs but appear to be getting cartesian join - lots of records per employee. I need one record only per employee with both the values:

select empid, newrate, newdate, oldrate, olddate.....

Thanks

 

[DotNetGnat]

please ignore my previous query....it has lot of errors...try this:

SELECT A.empID, A.rate, A.changeDate

From mytable A WHERE
changeDate in (
		Select Top 1 changeDate From mytable  B WHERE

changeDate in (
                Select Top 2 changeDate From mytable  C WHERE
                B.empID=C.empID  
                Order by changeDate ASC)
AND A.empID=B.empID Order by changeDate DESC)

-DNG

 

[DotNetGnat]

please interchange ASC and DESC keywords...

-DNG

 

[DotNetGnat]

may be its not the efficient way of doing this...but here is the query that also includes the PreviousRate and PreviousDate columns...

Select Z.empID, Z.rate, Z.changeDate,
X.rate as PreviousRate, X.changeDate as PreviousDate
FROM 
(Select empID, rate, changeDate
from mytable t1 WHERE t1.changeDate IN (
Select Top 1 changeDate from mytable t2
WHERE t1.empID=t2.empID order by changeDate Desc)) Z,
(SELECT A.empID, A.rate, A.changeDate
From mytable A WHERE
changeDate in (
		Select Top 1 changeDate From mytable B WHERE

changeDate in (
                Select Top 2 changeDate From mytable WHERE
                B.empID=C.empID
                Order by changeDate DESC)
AND A.empID=B.empID Order by changeDate ASC)) X 

WHERE Z.empID=X.empID

-DNG

 

[SQLDenis]

One way a little complicated, there must be a better way but I have to eat dinner now
tested and it works

create table blah (
empID int,
rate numeric(38,6),
changedate datetime
)


insert into blah
select 1001, 15.00, '20040101' union all
select 1001, 15.50, '20050101' union all
select 1001, 16.00 , '20060101' union all
select 1002, 13.50, '20030101' union all
select 1002, 16.00 , '20060101'

select b1.empid EmpID,b1.rate CurrentRate,b1.changedate CurrentDate,
b2.rate PreviousRate,b2.changedate PreviousDate
from blah b1 join blah b2 on b1.empid = b2.empid
and b2.changedate < b1.changedate
and b1.changedate in (select max(changedate) from blah group by empid)
and b2.changedate in (select max(changedate) from blah where changedate <b1.changedate group by empid)
group by b1.empid,b1.rate,b1.changedate,b2.rate,b2.changedate

I might take a look at it later again

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

No need to group at the end


create table blah (
empID int,
rate numeric(38,6),
changedate datetime
)


insert into blah
select 1001, 15.00, '20040101' union all
select 1001, 15.50, '20050101' union all
select 1001, 16.00 , '20060101' union all
select 1002, 13.50, '20030101' union all
select 1002, 16.00 , '20060101'

select b1.empid EmpID,b1.rate CurrentRate,b1.changedate CurrentDate,
b2.rate PreviousRate,b2.changedate PreviousDate
from blah b1 join blah b2 on b1.empid = b2.empid
and b2.changedate < b1.changedate
and b1.changedate in (select max(changedate) from blah group by empid)
and b2.changedate in (select max(changedate) from blah where changedate <b1.changedate group by empid)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

and another way use create table scripts from previous posts to test

select b1.empid EmpID,b1.rate CurrentRate,b1.changedate CurrentDate,
b2.rate PreviousRate,b2.changedate PreviousDate
from blah b1
join blah b2 on b1.empid =b2.empid
where b1.changedate in(
select top 2 changedate from blah
where empid =b1.empid order by changedate desc)
and b2.changedate in(
select top 2 changedate from blah
where empid =b2.empid order by changedate desc)
and b2.changedate < b1.changedate

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dukeslater]

Thanks to you both - I've tried both methods and they work with the sample data, but I have some nagging issues with production data. Shouldn't be a problem, but I wanted to say thanks now since I might not get it finished before the weekend.