user表
ID hiredate(入职时间) leavedate(离职时间) workstatus(工作状态)
离职了才有离职时间。 工作状态 A :在职， B:离职
以年份作为查询条件， 统计每月在职人数。这个SQL改怎么写啊？
跪求各位大神帮帮我！！！！！！！！！
------解决思路----------------------
SQL语句如下，如果要改年份，就改AllMonthDay语句段中的2014为指定的年份。
with rs as
(
select '张三' xm, CONVERT(datetime, '2014/04/01') hiredate, CONVERT(datetime, '2014/06/15') leavedate union all
select '李四' xm, CONVERT(datetime, '2013/09/10') hiredate, null leavedate union all
select '王五' xm, CONVERT(datetime, '2014/10/20') hiredate, CONVERT(datetime, '2014/12/01') leavedate union all
select '赵六' xm, CONVERT(datetime, '2014/03/31') hiredate, null leavedate union all
select '陈七' xm, CONVERT(datetime, '2014/10/02') hiredate, CONVERT(datetime, '2015/02/01') leavedate
)
, AllMonthDay as
(
select 1 Months, CONVERT(datetime, '2014/01/01') BeginDate, CONVERT(datetime, '2014/01/31') EndDate union all
select 2 Months, CONVERT(datetime, '2014/02/01') BeginDate, CONVERT(datetime, '2014/03/01')-1 EndDate union all
select 3 Months, CONVERT(datetime, '2014/03/01') BeginDate, CONVERT(datetime, '2014/03/31') EndDate union all
select 4 Months, CONVERT(datetime, '2014/04/01') BeginDate, CONVERT(datetime, '2014/04/30') EndDate union all
select 5 Months, CONVERT(datetime, '2014/05/01') BeginDate, CONVERT(datetime, '2014/05/31') EndDate union all
select 6 Months, CONVERT(datetime, '2014/06/01') BeginDate, CONVERT(datetime, '2014/06/30') EndDate union all
select 7 Months, CONVERT(datetime, '2014/07/01') BeginDate, CONVERT(datetime, '2014/07/31') EndDate union all
select 8 Months, CONVERT(datetime, '2014/08/01') BeginDate, CONVERT(datetime, '2014/08/31') EndDate union all
select 9 Months, CONVERT(datetime, '2014/09/01') BeginDate, CONVERT(datetime, '2014/09/30') EndDate union all
select 10 Months, CONVERT(datetime, '2014/10/01') BeginDate, CONVERT(datetime, '2014/10/31') EndDate union all
select 11 Months, CONVERT(datetime, '2014/11/01') BeginDate, CONVERT(datetime, '2014/11/30') EndDate union all
select 12 Months, CONVERT(datetime, '2014/12/01') BeginDate, CONVERT(datetime, '2014/12/31') EndDate
)
select a.Months, COUNT(*) zzrs from AllMonthDay a
left join rs b on b.hiredate<=a.EndDate and (b.leavedate>=a.BeginDate or b.leavedate is null)
group by a.Months