Finding Designation Of An Employee On A Given Date

Hi all,

I have two tables

CREATE TABLE [JEMP] (
[EMPID] [int] NOT NULL ,
[DESIGID] [int] NULL , -- CURRENT DESIGNATION OF EMPLOYEE
[DOB] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [JPRO] (
[PromoID] [int] IDENTITY (1, 1) NOT NULL ,
[EmpID] [int] NOT NULL ,
[EffectiveDate] [smalldatetime] NOT NULL ,
[NewDesigID] [int] NOT NULL , -- PROMOTED TO DESIGNATION
[DesigID] [int] NULL -- PROMOTED FROM DESIGNATION
) ON [PRIMARY]
GO


INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(1,1,'1962-03-11 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(2,25,'1980-10-7 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(3,8,'1978-04-05 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(4,7,'1962-07-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(5,22,'1973-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(6,55,'1971-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(7,11,'1973-09-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(8,22,'1975-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(9,22,'1977-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(10,23,'1984-07-11 00:00:00')



INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(3,'2002-15-11 00:00:00',7,20)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(3,'2003-03-01 00:00:00',8,7)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(4,'2002-01-04 00:00:00',20,22)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(4,'2005-05-01 00:00:00',7,20)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(5,'2001-10-01 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(6,'2001-08-01 00:00:00',55,NULL)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(7,'2003-10-01 00:00:00',11,8)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(8,'2001-09-01 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(9,'2002-01-05 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(10,'2002-11-01 00:00:00',24,25)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(10,'2003-11-15 00:00:00',23,24)

--
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.


With regards
Jatinder