现有表中有如下字段
工号(employee_number)
姓名(last_name)
组织(organization_id)
岗位(position_name)
岗位开始时间(effective_start_date)
岗位结束时间(effective_end_date)
我查询出的结果如下：
工号(employee_number) 姓名(last_name) 组织(organization_id) 岗位(position_name) 岗位开始时间(effective_start_date) 岗位结束时间(effective_end_date)
8610112 张三 办公室秘书科 文秘综合管理 2003-7-1 2007-10-31
8610112 张三 办公室秘书科 文秘综合管理 2007-11-1 2008-2-29
8610112 张三 办公室秘书科 文秘综合管理 2008-3-1 2008-12-31
8610112 张三 办公室秘书科 文秘综合管理 2009-1-1 2009-2-28
8610112 张三 办公室秘书科 文秘综合管理 2009-3-1 2009-3-30
8610112 张三 办公室秘书科 文秘综合管理 2009-3-31 2009-7-12
8610112 张三 计划推进科 投资项目管理 2009-7-13 2010-3-30
8610112 张三 计划推进科 投资项目管理 2010-3-31 2010-6-9
8610112 张三 经营管理科 统计 2010-6-10 2010-10-30
8610112 张三 经营管理科 统计 2010-10-31 2011-3-31
8610112 张三 经营管理科 副科长 2011-4-1 2011-8-31
8610112 张三 经营管理科 副科长 2011-9-1 2012-9-29
8610112 张三 经营管理科 副科长 2012-9-30 2013-11-30
8610112 张三 经营管理科 副科长 2013-12-1 2013-12-1
8610112 张三 经营管理科 副科长 2013-12-2 4712-12-31
说明：其中最后一条记录的岗位结束时间是“4712-12-31” 表示是当前人员的分配信息。
我想依据上述的结果查询出如下的效果该如何实现？ 多谢！
岗位年限(position_year)=同一个岗位的第最后一条岗位结束时间-该岗位第一条分配记录的开始时间
如果岗位结束时间是4712-12-31 则岗位年限=当前系统时间-该岗位的第一条分配记录开始时间
工号(employee_number) 姓名(last_name) 组织(organization_id) 岗位(position_name) 岗位开始时间(effective_start_date) 岗位结束时间(effective_end_date) 岗位年限(position_year)
8610112 张三 办公室秘书科 文秘综合管理 2003-7-1 2009-7-12 6
8610112 张三 计划推进科 投资项目管理 2009-7-13 2010-6-9 0.89
8610112 张三 经营管理科 统计 2010-6-10 2011-3-31 0.79
8610112 张三 经营管理科 副科长 2011-4-1 4712-12-31 3
------解决方案--------------------
--> 测试数据: [ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta] ([employee_number] int,[last_name] varchar(4),[organization_id] varchar(12),[position_name] varchar(12),[effective_start_date] datetime,[effective_end_date] datetime)
insert into [ta]
select 8610112,'张三','办公室秘书科','文秘综合管理','2003-7-1','2007-10-31' union all
select 8610112,'张三','办公室秘书科','文秘综合管理','2007-11-1','2008-2-29' union all
select 8610112,'张三','办公室秘书科','文秘综合管理','2008-3-1','2008-12-31' union all
select 8610112,'张三','办公室秘书科','文秘综合管理','2009-1-1','2009-2-28' union all
select 8610112,'张三','办公室秘书科','文秘综合管理','2009-3-1','2009-3-30' union all
select 8610112,'张三','办公室秘书科','文秘综合管理','2009-3-31','2009-7-12' union all
select 8610112,'张三','计划推进科','投资项目管理','2009-7-13','2010-3-30' union all
select 8610112,'张三','计划推进科','投资项目管理','2010-3-31','2010-6-9' union all
select 8610112,'张三','经营管理科','统计','2010-6-10','2010-10-30' union all
select 8610112,'张三','经营管理科','统计','2010-10-31','2011-3-31' union all
select 8610112,'张三','经营管理科','副科长','2011-4-1','2011-8-31' union all
select 8610112,'张三','经营管理科','副科长','2011-9-1','2012-9-29' union all
select 8610112,'张三','经营管理科','副科长','2012-9-30','2013-11-30' union all
select 8610112,'张三','经营管理科','副科长','2013-12-1','2013-12-1' union all
select 8610112,'张三','经营管理科','副科长','2013-12-2','4712-12-31'
;with
wang1 as(
select * from [ta] s
where not exists(select 1 from ta where employee_number=s.employee_number and position_name=s.position_name and effective_start_date<s.effective_start_date)
),
wang2 as(
select * from [ta] s
where not exists(select 1 from ta where employee_number=s.employee_number and position_name=s.position_name and effective_end_date>s.effective_end_date)
)
select wang1.*,工作年限=1.0*DATEDIFF(MONTH,wang1.effective_start_date,wang2.effective_end_date)/12
from wang1 join wang2 on wang1.employee_number=wang2.employee_number and wang1.position_name=wang2.position_name
8610112 张三 办公室秘书科 文秘综合管理 2003-07-01 00:00:00.000 2007-10-31 00:00:00.000 6.000000
8610112 张三 计划推进科 投资项目管理 2009-07-13 00:00:00.000 2010-03-30 00:00:00.000 0.916666
8610112 张三 经营管理科 统计 2010-06-10 00:00:00.000 2010-10-30 00:00:00.000 0.750000
8610112 张三 经营管理科 副科长 2011-04-01 00:00:00.000 2011-08-31 00:00:00.000 2701.666666
------解决方案--------------------
create table test (employee_number varchar(20),last_name varchar(20),organization_id varchar(20),
position_name varchar(20),effective_start_date datetime,effective_end_date datetime)
insert test
select '8610112','张三','办公室秘书科','文秘综合管理','2003-7-1','2007-10-31' union all
select '8610112','张三','办公室秘书科','文秘综合管理','2007-11-1','2008-2-29' union all
select '8610112','张三','办公室秘书科','文秘综合管理','2008-3-1','2008-12-31' union all
select '8610112','张三','办公室秘书科','文秘综合管理','2009-1-1','2009-2-28' union all
select '8610112','张三','办公室秘书科','文秘综合管理','2009-3-1','2009-3-30' union all
select '8610112','张三','办公室秘书科','文秘综合管理','2009-3-31','2009-7-12' union all
select '8610112','张三','计划推进科','投资项目管理','2009-7-13','2010-3-30' union all
select '8610112','张三','计划推进科','投资项目管理','2010-3-31','2010-6-9' union all
select '8610112','张三','经营管理科','统计','2010-6-10','2010-10-30' union all
select '8610112','张三','经营管理科','统计','2010-10-31','2011-3-31' union all
select '8610112','张三','经营管理科','副科长','2011-4-1','2011-8-31' union all