不使用MSsql或DB2或Oracle.
没有CTE.
没有OVERLAP谓词.
没有INTERVAL数据类型.
情况：对待修理的车辆工作直到无法启动
已收到为工作订购的所有零件.
零件可在维修开始前订购多次.
我们需要提取车辆在“零件保持”的时间
没有CTE.
没有OVERLAP谓词.
没有INTERVAL数据类型.
情况：对待修理的车辆工作直到无法启动
已收到为工作订购的所有零件.
零件可在维修开始前订购多次.
我们需要提取车辆在“零件保持”的时间
因此对于标识为id = 1的车辆
零件在4个不同的场合下订购(d1)并收到(d2)
ID d1 d2
1 8/1 8/8
1 8/2 8/6
1 8/12 8/14
1 8/3 8/10
8/1 8/8
d1 d2
|-------------------------------|
8/2 8/6 8/12 8/14
d1 d2 d1 d2
|---------------| |----------|
8/3 8/10
d1 d2
|---------------------|
8/1 8/14
|---------------------------------------------------------| = 13 days
8/10 8/12
|--------------------------------------| + |----------| = parts hold = 11 days
从上面可以看出,开始工作的等待时间(假设为8/1)
车辆可以工作的日期是13天.
等待零件所花费的实际时间是11天,这是数字
我们需要从数据中得出.
实际的日期时间数据将是我们将从中提取小时数的时间戳,
我们在此示例数据中使用日期以简化演示.
我们正在努力生成一个集合(不是psm,而不是udf,而不是游标)的解决方案.
TIA
解决方法
这个sql语句似乎得到了你想要的东西(t是样本表的表名)：
SELECT
d.id,d.duration,d.duration -
IFNULL(
( SELECT Sum( timestampdiff( sql_TSI_DAY,no_hold.d2,( SELECT min(d1) FROM t t4
WHERE t4.id = no_hold.id and t4.d1 > no_hold.d2 )))
FROM ( SELECT DISTINCT id,d2 FROM t t1
WHERE ( SELECT sum( IIF( t1.d2 between t2.d1 and t2.d2,1,0 ) )
FROM t t2 WHERE t2.id = t1.id and t2.d2 <> t1.d2 ) = 0
And d2 <> ( select max( d2 ) from t t3 where t3.id = t1.id )) no_hold
WHERE no_hold.id = d.id ),0 ) "parts hold"
FROM
( SELECT id,timestampdiff( sql_TSI_DAY,min( d1 ),max( d2 ) ) duration
FROM t GROUP BY id ) d
外部查询获取修复工作的持续时间.复杂子查询计算不等待零件的总天数.这是通过找到车辆没有等待零件的开始日期来完成的,然后计算它开始等待零件的天数：
// 1) The query for finding the starting dates when the vehicle is not waiting for parts,// i.e. finding all d2 that is not within any date range where the vehicle is waiting for part.
// The DISTINCT is needed to removed duplicate starting "no hold" period.
SELECT DISTINCT id,d2
FROM t t1
WHERE ( SELECT sum( IIF( t1.d2 between t2.d1 and t2.d2,0 ) ) from t t2
WHERE t2.id = t1.id and t2.d2 <> t1.d2 ) = 0 AND
d2 <> ( SELECT max( d2 ) FROM t t3 WHERE t3.id = t1.id ) )
// 2)车辆没有等待零件的日期是从上述查询到车辆//再次等待零件的日期
timestampdiff( sql_TSI_DAY,( SELECT min(d1) FROM t t4 WHERE t4.id = no_hold.id and t4.d1 > no_hold.d2 ) )
将上述两者结合起来并汇总所有这些时期,可以得出车辆不等待零件的天数.最终查询添加了一个额外条件来计算外部查询中每个id的结果.
在具有许多ID的非常大的表上,这可能不是非常有效.如果id被限制为一个或几个,它应该很好.
