我想使用DB2 SQL进行报告,该报告将在5月15日至17日之间显示持续时间(小时(列表。当前列表看起来像这样:
单击此处查看列表
您可以看到,从WO-391296
(第二行(中,它在ACTSTART
和ACTFINISH
中有重叠的时间。
我已经尝试了重叠功能。这是我的查询:
SELECT
*
FROM
workorder a,
workorder b
WHERE
a.wonum <> b.WONUM
AND (a.ACTSTART, a.ACTFINISH) OVERLAPS (b.ACTSTART, b.ACTFINISH)
AND DATE(a.ACTSTART) = '2019-05-15'
AND DATE(a.ACTFINISH) = '2019-05-17'
AND a.assetnum = 'A0000004'
AND DATE(b.ACTSTART) = '2019-05-15'
AND DATE(b.ACTFINISH) = '2019-05-17'
AND b.assetnum = 'A0000004' ;
但它不起作用,只是给我错误消息。:
SQL错误[42601]:在" tstart,a.actfinish(之后发现了意外的令牌"重叠"。预期令牌可能包括:" in" .. sqlcode = -104,sqlstate = 42601,驱动程序= 3.64.114
这是我实际的完整查询:
SELECT assetnum, wonum, worktype, actstart, actfinish,
(DAY(ACTFINISH-ACTSTART)*24 + HOUR(ACTFINISH-ACTSTART)) + FLOAT(MINUTE(ACTFINISH-ACTSTART))/60 AS wotime
FROM (
SELECT assetnum, wonum, WORKTYPE, ACTSTART,
(CASE WHEN ACTFINISH IS NULL THEN '2019-05-17-24.00.00' ELSE ACTFINISH END) AS ACTFINISH
FROM workorder ast
WHERE istask=0 AND siteid = 'SBY' AND WORKTYPE = 'CM' AND
(ast.assetnum = 'A0000004' or
assetnum in (select distinct assetnum from asset ast where ast.parent = 'A0000004') or
assetnum in (select distinct assetnum from asset ast where ast.parent
in (select distinct assetnum from asset ast where ast.parent = 'A0000004'))) AND
(
(status IN ('COMP', 'CONFIRM', 'CLOSE') AND
(date(ACTFINISH) BETWEEN '2019-05-15' and '2019-05-17')) OR
(status IN ('INPRG', 'COMP', 'CONFIRM', 'CLOSE') AND
(date(ACTSTART) <= '2019-05-17' AND (date(ACTFINISH) > '2019-05-17' OR ACTFINISH IS null)))
)
);
此列表的实际总持续时间为6小时,错误,因为我不想 SUM
重叠时间。
因此,最终结果必须为3.5小时(没有WO-391296
(。
2间隔重叠,如果:
start of the 1-st is <= of end of the 2-nd
和
start of the 2-nd is <= of end of the 1-st
create or replace function overlaps_dates (a_from date, a_to date, b_from date, b_to date)
returns int
deterministic
no external action
contains sql
return case when min(a_from, a_to) <= max(b_from, b_to) and min(b_from, b_to) <= max(a_from, a_to) then 1 else 0 end;