Posted by Erland Sommarskog on 06/08/05 01:51

jsfromynr (jatinder.singh@clovertechnologies.com) writes:
> I wish to find the designation of employee on given date by using
> promotion and master table . I am using the following query to get the
> result
>
> select isnull( ( select top 1 newdesigid from JPRO where
> empid=1 and effectivedate<'anygivendate' order by effectivedate desc )
> , (select desigid from empmast where empid=1) )
>
>
> It did give the result but looking for better method to solve this.

I don't see anything seriously wrong with that query. Here is an
alternate:

select Top 1 NewDesigID
from (select NewDesigID, EffectiveDate
from JPRO
where EmpID=7 and
EffectiveDate < '20050301'
union
select DESIGID, '19000101'
from JEMP
where EMPID=7) AS x
order by EffectiveDate desc

But I'm not sure in what way it would be "better".

If you are looking for a more ANSI way of doing it, you would have
to get the MAX(EffectiveDate) and then join back to the derived
table again. As you may guess, this is likely to be less effecient.
In SQL 2005, you could use a CTE (Common Table Expresssion) to avoid
repetition of the code for the derived table, but alas the query
plan is likely to be equally ineffecient.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]