时间区间统计问题

[cheng525jj]

有一个员工数据表A，字段如下：

Eid Name Costid JoinDate effectdate
222 张三 20 2016-01-03 2016-01-03
223 张三 50 2016-01-03 2016-01-30
224 张三 10 2016-01-03 2016-02-15
225 张三 60 2016-01-03 2016-03-03
226 张三 70 2016-01-03 2016-04-05

如何统计当costid变化时， 下一个effectdate减去前一个effectdate，计算出生效日期的区间，最后一条EndEffectdate永远为今天。


Eid Name Costid JoinDate effectdate StartEffectdate EndEffectdate
222 张三 20 2016-01-03 2016-01-03 2016-01-03 2016-01-29
223 张三 50 2016-01-03 2016-01-30 2016-01-30 2016-02-14
224 张三 10 2016-01-03 2016-02-15 2016-02-15 2016-03-02
225 张三 60 2016-01-03 2016-03-03 2016-03-03 2016-04-04
226 张三 70 2016-01-03 2016-04-05 2016-04-05 getdate()

[cheng525jj]

那位大虾能够来回答下？

[shoppo0505]

本来的懒的写了,看你这么急就写下吧
select
tb.*
, tb.effectdate as StartEffectdate
, ISNULL(DATEADD(DAY, -1, a.effectdate) , GETDATE())as EndEffectdate
from tb
LEFT OUTER join tb a on tb.Eid +1 = a.Eid

[道素]

WITH a(Eid,NAME,Costid,JoinDate ,effectdate) AS (
	select 222,N'张三 ', 20,'2016-01-03 ','2016-01-03' union all
	select 223 ,N'张三', 50,'2016-01-03','2016-01-30' union all
	select 224,N'张三', 10 ,'2016-01-03','2016-02-15' union all
	select 225,N'张三 ', 60,'2016-01-03','2016-03-03' union all
	select 226 ,N'张三',  70,'2016-01-03','2016-04-05'
)
SELECT a.*,a.effectdate AS StartEffectdate
                 ,isnull(dateadd(d,-1,b. nextEffect),GETDATE()) as EndEffectdate FROM a
OUTER APPLY (
	SELECT TOP 1 aa.effectdate AS nextEffect
     FROM a  AS aa WHERE aa.NAME=a.NAME AND aa.effectdate>a.effectdate ORDER BY aa.effectdate 
) b
/*
Eid	NAME	Costid	JoinDate	effectdate	StartEffectdate	EndEffectdate
222	张三 	20	2016-01-03 	2016-01-03	2016-01-03	2016-01-29 00:00:00.000
223	张三	50	2016-01-03	2016-01-30	2016-01-30	2016-02-14 00:00:00.000
224	张三	10	2016-01-03	2016-02-15	2016-02-15	2016-03-02 00:00:00.000
225	张三 	60	2016-01-03	2016-03-03	2016-03-03	2016-04-04 00:00:00.000
226	张三	70	2016-01-03	2016-04-05	2016-04-05	2016-03-08 09:55:49.010
*/