How to split record in multiple records from start/end date record

Question

I'm trying to split record to multiple record from start/end date in Oracle I have data like this

MachineID | start date | end date | running time | WC01 | 2019/09/05 07:00 | 2019/09/07 09:00 | 26:00 |

and I want to split record to each day from 08:00 to 08:00

MachineID | running date | running time | WC01 | 2019/09/05 | 1:00 | WC01 | 2019/09/06 | 24:00 | WC01 | 2019/09/07 | 1:00 |

Thank you for your help!

Answer 1

You can try below query:

SELECT
    MACHINEID,
    RUNNING_DATE,
    DECODE(RUNNING_DATE, TRUNC(START_DATE), CASE
        WHEN DIFF_START < 0  THEN 0
        WHEN DIFF_START > 12 THEN 12
        ELSE DIFF_START
    END, TRUNC(END_DATE), CASE
        WHEN DIFF_END < 0  THEN 0
        WHEN DIFF_END > 12 THEN 12
        ELSE DIFF_END
    END, 24) AS RUNNING_HOURS
FROM
    (
        SELECT
            MACHINEID,
            RUNNING_DATE,
            ROUND(24 *((TRUNC(START_DATE + LVL - 1) + 8 / 24) - START_DATE)) AS DIFF_START,
            ROUND(24 *(END_DATE -(TRUNC(START_DATE + LVL - 1) + 8 / 24))) AS DIFF_END,
            START_DATE,
            END_DATE
        FROM
            (
                SELECT
                    DISTINCT MACHINEID,
                    LEVEL   AS LVL,
                    START_DATE,
                    END_DATE,
                    TRUNC(START_DATE + LEVEL - 1) AS RUNNING_DATE
                FROM
                    YOURTABLE
                CONNECT BY
                    LEVEL <= TRUNC(END_DATE) - TRUNC(START_DATE) + 1
            )
    );

db<>fiddle demo

Change the logic wherever it is not meeting your requirement. I have created the query taking sample data and expected output into consideration.

Cheers!!

Answer 2

We can handle this via the help from a calendar table which contains all dates you expect to appear in your data set, along with a separate record for each minute:

WITH dates AS (
    SELECT TIMESTAMP '2019-09-05 00:00:00' + NUMTODSINTERVAL(rownum, 'MINUTE') AS dt
    FROM dual
    CONNECT BY level <= 5000
)

SELECT
    m.MachineID,
    TRUNC(d.dt) AS running_date,
    COUNT(t.MachineID) / 60 AS running_hours
FROM dates d
CROSS JOIN (SELECT DISTINCT MachineID FROM yourTable) m
LEFT JOIN yourTable t
    ON d.dt >= t.start_date AND d.dt < t.end_date
WHERE
    TO_CHAR(d.dt, 'HH24') >= '08' AND TO_CHAR(d.dt, 'HH24') < '21'
GROUP BY
    m.MachineID,
    TRUNC(d.dt)
ORDER BY
    TRUNC(d.dt);

Demo