A table is a roaster, day three class, one early, corresponding to three periods,
Such as the early: 20200303 07:29:59 00:00:00-220303
Survey: 20200303 17:29:59 07:30:00-20200303
Evening: 20200303 29:59 17:30:00-20200303:59;
Another table is process schedule, start time and end time, such as 20200303 15:26:23 04:25:23-20200303
Requirements:
Calculate the working procedure, the divisions of the corresponding a process can only correspond to one class, the existence of processes across class, as the above example, if exist across class, where are a class of stay for long time, take the flight;
Have trouble thinking or in SQL will do help to solve how to achieve!
CodePudding user response:
Direct execution of this statement, here will return flight, and the length of every shift, finally sorted by the flight time,WITH
"Roaster AS
"(
SELECT '00:00:00' B, '07:29:59' E 'morning' T FROM DUAL UNION ALL
SELECT '07:30:00' B, '17:29:59' E 'GSM' T FROM DUAL UNION ALL
SELECT '17:30:00' B, '23:59:59' E 'night' T FROM DUAL
),
"Process schedule" AS (SELECT '2020-03-03 04:25:23' B, 'the 2020-03-03 15:26:23 E FROM DUAL),
T3 AS (SELECT TO_DATE (T2), B '- the DD YYYY - MM HH24: MI: SS') + (LEVEL 1)/24/60/60 D FROM "process schedule" T2 CONNECT BY LEVEL & lt;=
(TO_DATE (T2), E '- DD YYYY - MM HH24: MI: SS') - TO_DATE (T2), B '- the DD YYYY - MM HH24: MI: SS')) * 24 * 60 * 60 + 1),
T4 AS (SELECT T3. *, T.T, TO_DATE (TO_CHAR (T3) D, 'YYYY - MM - DD) | | "| | T.B,' YYYY - MM - DD HH24: MI: SS ') D1,
TO_DATE (TO_CHAR (T3) D, 'YYYY - MM - DD) | | "| | T.E,' YYYY - MM - DD HH24: MI: SS ') D2 FROM T3 JOIN" roaster T ON 1=1 ")
SELECT T "shifts the category", the SUM (
CASE the WHEN D BETWEEN D1 AND D2 THEN 1 ELSE 0 END
"Flight time (in seconds)" FROM T4 GROUP BY T ORDER BY DESC