我想从员工名册和合同记录表中查询出给定日期的唯一且最后一条记录,
Select
A2.[登记月份],A2.[编号],A2.[姓名],A2.[部门],A2.[班组],A2.[首签日期],A2.[续签日期],A2.[合同止期],A2.[辞退日期],A2.[剩余天数],A2.[性别],A2.[职务],A2.[职称],A2.[学历],A2.[专业],A2.[从业证],A2.[银行卡号],A2.[基本工资],A2.[月度考核],A2.[公积金],A2.[社会保险],A2.[起征额]
from
(
Select A.[登记月份],A.[编号],C.[姓名],A.[部门],A.[班组],A.[首签日期],A.[续签日期],A.[合同止期],A.[辞退日期],A.[剩余天数],C.[性别],A.[职务],A.[职称],A.[学历],A.[专业],A.[从业证],A.[银行卡号],A.[基本工资],A.[月度考核],A.[公积金],A.[社会保险],A.[起征额]
from 合同记录 A
inner join ( select 登记日期,姓名,编号,性别 from 员工名册 ) C on A.[编号]=C.[编号]
) A2
inner join
( select max(合同记录.[登记月份]) as 登记月份,合同记录.[编号]
from 合同记录
where 合同记录.[登记月份] < '2013-10-1'
group by 合同记录.[编号]
) B on A2.[登记月份]={B}.[登记月份] and A2.[编号]=B.[编号]
where A2.[辞退日期] is null or A2.[辞退日期] > '2013-9-30'
order by A2.[编号]
<'2013-10-1'和> '2013-9-30' 用日期输入框输入,我不会往下做了,请帮助修改。