求一个聚合的SQL该怎么写
有这样的数据
员工 开始日期 终了日期
A 2013-05-01 00:00:00.000 2013-06-08 00:00:00.000
A 2013-09-02 00:00:00.000 2013-12-31 00:00:00.000
A 2013-09-05 00:00:00.000 NULL
求员工A的所能表示的最大范围的日期,取日期的并集,最后结果应该如下:
员工 开始日期 终了日期
A 2013-05-01 00:00:00.000 2013-06-08 00:00:00.000
A 2013-09-02 00:00:00.000 NULL
请问谁知道这样的SQL语句该如何写
SQL,并集
[解决办法]
;with t(员工 , 开始日期,终了日期)
as
(
select 'A', '2013-05-01 00:00:00.000','2013-06-08 00:00:00.000'
union all select 'A','2013-09-02 00:00:00.000','2013-12-31 00:00:00.000'
union all select 'A','2013-09-05 00:00:00.000',NULL
union all select 'A','2013-09-15 00:00:00.000','2013-11-08 00:00:00.000'
),
tt
as
(
select 员工,开始日期,终了日期,
(select MIN(t2.开始日期)
from t t2
where t1.员工 = t2.员工
and t1.开始日期 between t2.开始日期 and isnull(t2.终了日期,'3000-01-01')
) as min_开始日期,
(select max(isnull(t2.终了日期,'3000-01-01'))
from t t2
where t1.员工 = t2.员工
and t1.开始日期 between t2.开始日期 and isnull(t2.终了日期,'3000-01-01')
) as max_终了日期
from t t1
)
select 员工,min_开始日期 as 开始日期,
nullif(max(max_终了日期),'3000-01-01') as 终了日期
from tt
group by 员工,min_开始日期
/*
员工 开始日期 终了日期
A 2013-05-01 00:00:00.0002013-06-08 00:00:00.000
A 2013-09-02 00:00:00.000NULL
*/