求助！剔除case finish 到下一次case reopen之间的时间

Question

对于：用case CSTAT为C的CALLDATE减去第一条记录得到总的CASE处理时间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
)
select * from t_casealltime;对于：然后剔除中间CASE CSTAT为F 到下条CASE状态不为F的记录之间的CALLDATE的差值
你必须说明更多的规则，F 和 I 之间的关系，是一一对应吗？

Answer 1

如果 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;

Answer 2

将楼主的算法换个等价的：计算每段的持续时间，合计非F标记的持续时间。
借用2楼的起头两段，假定C标记的就是最后一条，就写在条件中了。
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_time AS
(
SELECT T1.*, 
       CONVERT(float,T2.CALLDATE-T1.CALLDATE) days
  FROM t_order T1
  LEFT JOIN t_order T2
         ON T1.Caseno = T2.Caseno
        AND T1.od + 1 = T2.od
)
--SELECT *,CONVERT(datetime,days) FROM t_time
SELECT SUM(days) days
  FROM t_time
 WHERE CSTAT <> 'F'
                  days
----------------------
                 0.875
这是 t_time 的内容
Caseno        CSTAT CALLDATE                                  od                   days 
------------- ----- ----------------------- -------------------- ---------------------- -----------------------
2014073112501 I     2014-07-31 00:00:00.000                    1                   0.25 1900-01-01 06:00:00.000
2014073112501 F     2014-07-31 06:00:00.000                    2       3.79166666666667 1900-01-04 18:59:59.997
2014073112501 I     2014-08-04 01:00:00.000                    3      0.416666666666667 1900-01-01 10:00:00.000
2014073112501 F     2014-08-04 11:00:00.000                    4      0.208333333333333 1900-01-01 05:00:00.000
2014073112501 I     2014-08-04 16:00:00.000                    5      0.208333333333333 1900-01-01 05:00:00.000
2014073112501 F     2014-08-04 21:00:00.000                    6     0.0833333333333333 1900-01-01 02:00:00.000
2014073112501 C     2014-08-04 23:00:00.000                    7                   NULL NULL

Answer 3

69小时，进位成3天
再看了一遍，理解没错啊！
应该是 6+10+25=21 小时
不好意思，我没权限编辑发帖内容，其实我有需要补充的数据
1 2014073112501   I  2014-07-31 00:00:00.000
2 2014073112501   I  2014-08-01 06:00:00.000
3 2014073112501   F  2014-08-02 06:00:00.000
4 2014073112501   F  2014-08-03 06:00:00.000
5 2014073112501   I  2014-08-04 01:00:00.000
6 2014073112501   F  2014-08-04 11:00:00.000
7 2014073112501   I  2014-08-04 16:00:00.000
8 2014073112501   F  2014-08-04 21:00:00.000
9 2014073112501   C  2014-08-04 23:00:00.000I in progess
F finish
C close其实这个模拟数据就是CRM系统对于客户来电问题的处理记录我只需要统计出1-3，5-6，7-8之间的时间合计一下计算出case实际处理时间就可以了但由于数据是第三方提供的，架构上已经没法改变了，只能这么麻烦的算下来并且计算出来的实际处理时间如果不满1天就进位为一天
这个例子的用时是69小时，结算成3天

Answer 4

下面的精度是按秒算的（多一秒就算一天），你按照需要的精度调整。
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
)
--SELECT *,CONVERT(datetime,days) FROM t_time
SELECT (SUM(seconds)+86399)/86400 days,
       DATEADD(second,SUM(seconds),'1900-01-01')
  FROM t_time
 WHERE CSTAT <> 'F'
days        
----------- -----------------------
3           1900-01-03 21:00:00.000

Answer 5

再次感谢，这个答案已经make sure了也感谢其他热心回答的题友，开始散分