Needs to be a better way to query this.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Needs to be a better way to query this.

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-12-14 : 11:48:41

I have a table with the following logic.


Declare @MyTable (EmployeeID int,StatusID int,EffectiveDate datetime)
INsert into @MyTable
select 1,1,'01/01/2004' Union all
select 1,4,'01/01/2005' Union all
select 1,1,'01/01/2006' Union all
select 1,5,'01/01/2007' Union all
select 1,1,'01/01/2004' Union all
select 2,1,'01/01/2005' Union all
select 2,5,'01/01/2006' Union all
select 2,1,'01/01/2007' Union all
select 2,5,'01/01/2008' Union all
select 2,1,'01/01/2009'

Basically for each employeeID, I need to get the date of the last statusID that is equal to 1 that follows any status <> to 5. So if the statusID = any # besides 5 I would take the following effectivedate = 1, but if there is no prior status dates, or if the status = 5 I would tread those as null and take the first status that shows up which is equal to 1 and does not have a status <> 1 after.

So the results would be

EmployeeID StatusID EffectiveDate
1,1,'01/01/2006'
2,1,'01/01/2005'
3,1,'01/01/2007'
4,1,'01/01/2009'

I have accomplished this currently by running a messy sub-query in a case statement, but there has got to be a better way. Any sugestions on the best approach for this. I'm re-working the query now, and will post updates.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-14 : 11:58:16

Where did EmployeeIDs 3 and 4 come from?

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-12-14 : 12:01:59

here's the solution I came up with.

Ryan, it appears I didn't post a complete data set.



Declare @MyTable table (EmployeeID int,StatusID int,EffectiveDate datetime)
INsert into @MyTable
select 1,1,'01/01/2004' Union all
select 1,4,'01/01/2005' Union all
select 1,1,'01/01/2006' Union all
select 1,5,'01/01/2007' Union all
select 1,1,'01/01/2004' Union all
select 2,1,'01/01/2005' Union all
select 2,5,'01/01/2006' Union all
select 2,1,'01/01/2007' Union all
select 2,5,'01/01/2008' Union all
select 2,1,'01/01/2009' Union all
select 3,1,'01/01/2007' Union all
select 3,5,'01/01/2008' Union all
select 3,1,'01/01/2009' Union all
select 4,1,'01/01/2007' Union all
select 4,6,'01/01/2008' Union all
select 4,1,'01/01/2009' 

select * 
from
(
select Row_Number() over (Partition by a.EmployeeID order by effectivedate asc) as RowID,*
from 
@MyTable a
where a.StatusID <> 5
and a.EffectiveDate > isnull((Select max(EffectiveDate) 
							 from 
							@MyTable aa
							where not aa.StatusID in (5,1)
							and aa.EmployeeID = a.EmployeeID
							),'1/1/1900')
) t
where 
t.ROwID = 1

RowID EmployeeID StatusID EffectiveDate
1 1 1 2006-01-01 00:00:00.000
1 2 1 2005-01-01 00:00:00.000
1 3 1 2007-01-01 00:00:00.000
1 4 1 2009-01-01 00:00:00.000

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources