我有一个表A，里面记录着员工每天的数据，其中表A有给字段Status标识该员工是临时工，还是正式工
表结构如下：
Staff_ID EffectiveDate Status Hour
CH001 2012-10-01 PT 8
CH001 2012-10-02 PT 7
CH001 2012-10-03 PT 8
CH001 2012-10-04 FT 9
... ... .. ..
我想写个语句获取2012-10-01到2012-10-31期间，Status由PT转变为FT所有员工的数据，应该怎样获取？
一定要是PT转变为FT的
------解决方案--------------------
update a set status='FT' where EffectiveDate between '2012-10-01' and '2012-10-31' and status='PT'
------解决方案--------------------
--SQL
--要是正式工，其最后一次一定为FT
select M.Staff_ID from
(select A.Staff_ID from
(select distinct Staff_ID,Status from TableA where EffectiveDate>'2012-10-01' and EffectiveDate<'2012-10-31') A
group by A.Staff_ID
having COUNT(Status)>1) M inner join
(select *From TableA A where EffectiveDate=
(select MAX(EffectiveDate) EffectiveDate from TableA where Staff_ID = A.Staff_ID)) N
on M.Staff_ID=N.Staff_ID
where N.Status='FT'
--结果集
/*
Staff_ID
CH001
*/