Crear historia de cambios en una tabla

Lo que aquí tienes es un trigger que genera una tabla de histórico. El objeto es que cuando un Empleado cambie de adscripción, se registre un movimiento con la salida de la adscripción anterior y la nueva situación. El histórico guarda para cada empleado la fecha de entrada y la fecha de salida en una sección. Si el empleado está todavía en la sección en el histórico hay un null en la fecha de salida. Podríamos hacer que el trigger no grabase el registro último para no tener que actualizarlo después, pero a mi me parece más elegante esta solución.
Si en un futuro queremos saber los movimientos que ha tenido un empleado, podemos en nuestra select cambiar los nulos por la fecha actual, de tal forma que tendríamos un listado que diría que el empleado 3 está en la sección 1 desde una fecha hasta hoy. Para eso podríamos poner en la select un isnull(Campo, getdate()) y resuelto.
El Código
if not objectproperty(object_id('EmpleadoAdscripcion'),'IsTable') is null
drop table empleadoadscripcion
go
create table empleadoAdscripcion (idEmpleado int not null ,idAdscripcion int not null,fecha datetime)
Go
Alter table empleadoAdscripcion add constraint pk_ead primary key (idEmpleado)
Go
if not objectproperty(object_id('histempads'),'IsTable') is null
drop table histempads
go
create table histempads (idEmpleado int, idAdscripcion int, FechaInicial datetime, FechaFinal datetime)
Go
Create index ind_hitem on histempads (idEmpleado, idAdscripcion)
Go
create trigger trg_eads on empleadoadscripcion for insert, update
as
begin
-- Primero actualizo la fecha de salida del registro anterior ...
update HistEmpAds Set HistEmpAds.fechaFinal=i.Fecha
from Inserted i inner join Deleted d on i.idEmpleado=d.idEmpleado
where HistEmpAds.idEmpleado= i.IdEmpleado and HistEmpAds.idAdscripcion = d.idAdscripcion
-- Ahora insertamos el movimiento
insert HistEmpAds
select idEmpleado,idAdscripcion,Fecha,null from inserted
end
go
insert into empleadoAdscripcion values (1,1,'20010101')
go
update empleadoAdscripcion set idAdscripcion=2,fecha='20010110' where idempleado=1
go
insert into empleadoAdscripcion values (2,2,'20010101')
go
update empleadoAdscripcion set fecha=getdate(),idadscripcion=3
go
select * From histempads

Hola marcelocf:
Creo entender la lógica de tu respuesta, ¿ahora cómo sería el select para saber el estado en una fecha determinada?
La verdad que no había pensado en esta variante

select * from t1s where fecha between @FchIni and @FchFin