假设有一组数据
Caseno CSTAT CALLDATE
2014073112501 I 2014-07-31 00:00:00.000
2014073112501 F 2014-07-31 06:00:00.000
2014073112501 I 2014-08-04 01:00:00.000
2014073112501 F 2014-08-04 11:00:00.000
2014073112501 I 2014-08-04 16:00:00.000
2014073112501 F 2014-08-04 21:00:00.000
2014073112501 C 2014-08-04 23:00:00.000
用case CSTAT为C的CALLDATE减去第一条记录得到总的CASE处理时间
然后剔除中间CASE CSTAT为F 到下条CASE状态不为F的记录之间的CALLDATE的差值
我需要生成为一行记录
如
caseno time（天）
2014073112501 X
这个sql该如何入手写，我很费解，求大神赐招
------解决方案--------------------
如果 F 和 F 后面的非 F 是一一对应的话：
with t (Caseno, CSTAT, CALLDATE)
as
(
select '2014073112501','I', cast('2014-07-31 00:00:00.000' as datetime) union all
select '2014073112501','F', cast('2014-07-31 06:00:00.000' as datetime) union all
select '2014073112501','I', cast('2014-08-04 01:00:00.000' as datetime) union all
select '2014073112501','F', cast('2014-08-04 11:00:00.000' as datetime) union all
select '2014073112501','I', cast('2014-08-04 16:00:00.000' as datetime) union all
select '2014073112501','F', cast('2014-08-04 21:00:00.000' as datetime) union all
select '2014073112501','C', cast('2014-08-04 23:00:00.000' as datetime)
),
t_order as
(
select *, ROW_NUMBER() over (partition by Caseno order by CALLDATE) od
from t
),
t_casealltime as
(
select caseno, MAX(Case when od=1 then calldate else null end) as starttime,
MAX(case when cstat = 'C' then CALLDATE else null end) as endtime from t_order
group by Caseno
),
t_first_f as
(
select caseno, MIN(CALLDATE) first_F_time from t
where CSTAT = 'F'
group by caseno
),
t_f_order as
(
select t.Caseno, t.CALLDATE, ROW_NUMBER() over (PARTITION by t.Caseno order by t.CALLDATE) od
from t, t_first_f where t.Caseno = t_first_f.Caseno and t.CSTAT = 'F'
and t.CALLDATE >= t_first_f.first_F_time
),
t_nonf_order as
(
select t.Caseno, t.CALLDATE, ROW_NUMBER() over (PARTITION by t.Caseno order by t.CALLDATE) od
from t, t_first_f where t.Caseno = t_first_f.Caseno and t.CSTAT <> 'F'
and t.CALLDATE >= t_first_f.first_F_time
),
t_diff as
(
select a.Caseno, sum(DATEDIFF(minute, a.calldate, b.calldate)) diff
from t_f_order a, t_nonf_order b
where a.Caseno = b.Caseno and a.od = b.od
group by a.Caseno
)
select a.Caseno, a.starttime, a.endtime, DATEDIFF(minute, a.starttime, a.endtime) all_minutes,
b.diff
from t_casealltime A left join t_diff b on b.Caseno = a.Caseno;
------解决方案--------------------
下面的精度是按秒算的（多一秒就算一天），你按照需要的精度调整。
with t (Caseno, CSTAT, CALLDATE)
as
(
select '2014073112501','I', cast('2014-07-31 00:00:00.000' as datetime) union all
select '2014073112501','I', cast('2014-08-01 06:00:00.000' as datetime) union all
select '2014073112501','F', cast('2014-08-02 06:00:00.000' as datetime) union all
select '2014073112501','F', cast('2014-08-03 06:00:00.000' as datetime) union all
select '2014073112501','I', cast('2014-08-04 01:00:00.000' as datetime) union all
select '2014073112501','F', cast('2014-08-04 11:00:00.000' as datetime) union all
select '2014073112501','I', cast('2014-08-04 16:00:00.000' as datetime) union all
select '2014073112501','F', cast('2014-08-04 21:00:00.000' as datetime) union all
select '2014073112501','C', cast('2014-08-04 23:00:00.000' as datetime)
),
t_order as
(
select *, ROW_NUMBER() over (partition by Caseno order by CALLDATE) od
from t
),
t_time AS
(
SELECT T1.*,
DATEDIFF(second,T1.CALLDATE,T2.CALLDATE) seconds
FROM t_order T1
LEFT JOIN t_order T2
ON T1.Caseno = T2.Caseno
AND T1.od + 1 = T2.od