Hi, I have a query that has been sent over by a colleague but unfortunately I am unable to get hold of him at the mo. Unfortunately I have had a look at the query but having not used CAST MULTISET and LEVEL before, I am trying to understand what the query is trying to do.
Long shot, but would appreciate it if somebody could shed some light as to what the query is trying to do.
The data table where the data sits is the following:
CREATE TABLE "TEST_BUS_HRS"
( "TASK" VARCHAR2(100 BYTE),
"START_" DATE,
"END_" DATE
)
Insert Statements:
Insert into TEST_BUS_HRS (TASK,START_,END_) values ('A',to_date('16-JAN-17 10:00:00','DD-MON-RR HH24:MI:SS'),to_date('23-JAN-17 11:35:00','DD-MON-RR HH24:MI:SS'));
Insert into TEST_BUS_HRS (TASK,START_,END_) values ('B',to_date('18-JAN-17 17:53:00','DD-MON-RR HH24:MI:SS'),to_date('19-JAN-17 08:40:00','DD-MON-RR HH24:MI:SS'));
Insert into TEST_BUS_HRS (TASK,START_,END_) values ('C',to_date('13-JAN-17 13:00:00','DD-MON-RR HH24:MI:SS'),to_date('17-JAN-17 14:52:00','DD-MON-RR HH24:MI:SS'));
Insert into TEST_BUS_HRS (TASK,START_,END_) values ('D',to_date('21-JAN-17 17:59:00','DD-MON-RR HH24:MI:SS'),to_date('30-JAN-17 08:52:00','DD-MON-RR HH24:MI:SS'));
Query:
SELECT TASK,
SUM( END_ - START_ ) * 24 AS TOTAL_HOURS
FROM (
SELECT TASK,
GREATEST( T.START_, D.COLUMN_VALUE + INTERVAL '8' HOUR ) AS START_, -- BUSINESS START HOURS FROM 0800 HRS
LEAST( T.END_, D.COLUMN_VALUE + INTERVAL '18' HOUR ) AS END_ -- BUSINESS END HOURS AT 1800 HOURS
FROM TEST_BUS_HRS T
CROSS JOIN TABLE (
CAST (
MULTISET (
SELECT TRUNC( T.START_ + LEVEL - 1 )
FROM DUAL
WHERE TRUNC( T.START_ + LEVEL - 1 ) - TRUNC( T.START_ + LEVEL - 1, 'IW' ) < 6
CONNECT BY TRUNC( T.START_ + LEVEL - 1 ) < T.END_
) AS SYS.ODCIDATELIST
)
) D
)
GROUP BY TASK;
Unfortunately, I have been asked to run the query, which is fine, but need to understand it so that I know the results I am getting are correct. From what I can gather it is trying to workout how long a task is taking but based on business hours which is Mon - Sat 8am to 6pm but need to understand the initial query (Cross Join etc)
Message was edited by: 976439