OraFAQ Forum: SQL & PL/SQL » Visualize Processes Vertical

Home » SQL & PL/SQL » SQL & PL/SQL » Visualize Processes Vertical (Oracle 12)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Visualize Processes Vertical [message #665072]

Mon, 21 August 2017 03:36

quirks
Messages: 82
Registered: October 2014

Member

Hello,

I've got a tricky one and can't solve it. Hopefully someone can point me in the right direction.

we have a table in which processes with their start and end time are listed. Here is a simplified example (the create script is in the spoiler):

PROCESS | START_DATE | END_DATE
--------+------------+-----------
1       | 01.01.2018 | 05.01.2018
2       | 01.01.2018 | 03.01.2018
3       | 03.01.2018 | 10.01.2018
4       | 06.01.2018 | 10.01.2018
5       | 06.01.2018 | 08.01.2018
6       | 09.01.2018 | 14.01.2018
7       | 11.01.2018 | 12.01.2018

Toggle Spoiler

I'd like to create a SQL in which the processes are listed beneath each other by day. Here is an example of the expected output:

DAY        |Position_1 |Position_2 |Position_3 |Position_4 |Position_5 |Position_6 |Position_7 |Position_8 |Position_9 |Position_10|
-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
01.01.2018 |          1|          2|           |           |           |           |           |           |           |           |
02.01.2018 |          1|          2|           |           |           |           |           |           |           |           |
03.01.2018 |          1|          3|           |           |           |           |           |           |           |           |
04.01.2018 |          1|          3|           |           |           |           |           |           |           |           |
05.01.2018 |           |          3|           |           |           |           |           |           |           |           |
06.01.2018 |          4|          3|          5|           |           |           |           |           |           |           |
07.01.2018 |          4|          3|          5|           |           |           |           |           |           |           |
08.01.2018 |          4|          3|           |           |           |           |           |           |           |           |
09.01.2018 |          4|          3|          6|           |           |           |           |           |           |           |
10.01.2018 |           |           |          6|           |           |           |           |           |           |           |
11.01.2018 |          7|           |          6|           |           |           |           |           |           |           |
12.01.2018 |           |           |          6|           |           |           |           |           |           |           |
13.01.2018 |           |           |          6|           |           |           |           |           |           |           |

The Rule is, that each process should occur in the first most possible position. If there are two processes that could occupy the same position, then, the one with the earliest START_DATE should come first. If the START_DATE is not sufficient enough to decide which one comes first, then the one with the longest duration shall win. And if even that is not enough the one with the lowest process number shall come first.

The END_DATE of the process is not part of the productive time. If the END_DATE is '05.01.2018' the process is finished on '04.01.2018 23:59:59'.

We can only have 6 parallel processes, but I've increased the amount of columns to 10 ... just in case.

What I've come up with is the following statement, but the output is a mess. I'd be glad if someone could give me a hint how to improve it.

WITH
    DATES
    AS
        (SELECT DAY
           FROM (SELECT     TO_DATE('01.01.1901', 'DD.MM.YYYY') + ROWNUM - 1 AS DAY
                       FROM DUAL
                 CONNECT BY LEVEL <= TO_DATE('31.12.2999', 'DD.MM.YYYY') - TO_DATE('01.01.1901', 'DD.MM.YYYY') + 1)
          WHERE DAY BETWEEN TO_DATE('01.01.2018', 'DD.MM.YYYY') AND TO_DATE('20.01.2018', 'DD.MM.YYYY')),
    PROCESSES
    AS
        (SELECT DISTINCT PROCESS
                        ,END_DATE - START_DATE AS DURATION
                        ,DAY
                        ,START_DATE
                        ,END_DATE
           FROM TEST_PROJ, DATES
          WHERE DATES.DAY BETWEEN TEST_PROJ.START_DATE AND TEST_PROJ.END_DATE),
    PROC_RANKING
    AS
        (SELECT PROCESS
               ,DURATION
               ,DAY
               ,START_DATE
               ,END_DATE
               ,RANK() OVER(PARTITION BY DAY ORDER BY START_DATE, DURATION DESC, PROCESS) AS PROCESS_RANK
           FROM PROCESSES
          WHERE DAY <> END_DATE),
    PROC_ORDER
    AS
        (SELECT DISTINCT PROC_RANKING.PROCESS, MAX(PROC_RANKING.PROCESS_RANK) OVER (PARTITION BY PROC_RANKING.PROCESS) AS PROCES_POSITION
           FROM PROC_RANKING),
    TOGETHER
    AS
        (SELECT PROCESSES.PROCESS, PROCESSES.DAY, PROC_ORDER.PROCES_POSITION
           FROM PROCESSES, PROC_ORDER
          WHERE PROCESSES.PROCESS = PROC_ORDER.PROCESS AND PROCESSES.DAY <> PROCESSES.END_DATE)
(SELECT *
   FROM TOGETHER
        PIVOT
            (MAX(TOGETHER.PROCESS)
            FOR PROCES_POSITION
            IN (1 AS "Position_1"
              ,2 AS "Position_2"
              ,3 AS "Position_3"
              ,4 AS "Position_4"
              ,5 AS "Position_5"
              ,6 AS "Position_6"
              ,7 AS "Position_7"
              ,8 AS "Position_8"
              ,9 AS "Position_9"
              ,10 AS "Position_10")))
ORDER BY DAY;

[Updated on: Mon, 21 August 2017 04:20]

Report message to a moderator

Re: Visualize Processes Vertical [message #665076 is a reply to message #665072]

Mon, 21 August 2017 08:23

ALEXWE
Messages: 11
Registered: August 2016
Location: Germany

Junior Member

PROCESS | START_DATE | END_DATE
--------+------------+-----------
1       | 01.01.2018 | 05.01.2018
2       | 01.01.2018 | 03.01.2018
3       | 03.01.2018 | 10.01.2018
4       | 06.01.2018 | 10.01.2018
5       | 06.01.2018 | 08.01.2018
6       | 09.01.2018 | 14.01.2018
7       | 11.01.2018 | 12.01.2018

with 
process
as (
(select 1 process, TO_DATE('01.01.2018', 'DD.MM.YYYY')start_date, TO_DATE('05.01.2018', 'DD.MM.YYYY') end_date from dual)
union all (select 2, TO_DATE('01.01.2018', 'DD.MM.YYYY'), TO_DATE('03.01.2018', 'DD.MM.YYYY') from dual)
union all (select 3, TO_DATE('03.01.2018', 'DD.MM.YYYY'), TO_DATE('10.01.2018', 'DD.MM.YYYY') from dual)
union all (select 4, TO_DATE('06.01.2018', 'DD.MM.YYYY'), TO_DATE('10.01.2018', 'DD.MM.YYYY') from dual)
union all (select 5, TO_DATE('06.01.2018', 'DD.MM.YYYY'), TO_DATE('08.01.2018', 'DD.MM.YYYY') from dual)
union all (select 6, TO_DATE('09.01.2018', 'DD.MM.YYYY'), TO_DATE('14.01.2018', 'DD.MM.YYYY') from dual)
union all (select 7, TO_DATE('11.01.2018', 'DD.MM.YYYY'), TO_DATE('12.01.2018', 'DD.MM.YYYY') from dual)
)
,dates as 
(select to_date(case when length(level) = 1 then '0' else null end || level||'01.2018','dd.mm.yyyy') day from dual connect by level < 14)
select day, min(positon_1),min(position_2),min(position_3),min(position_4),min(positioN_5),min(position_6), min (position_7) from(
select dates.day,row_number() over(partition by day order by start_date,process) position,process.process, process.start_Date,process.end_date   from dates 
join process
on dates.day >= start_date
and dates.day < end_date
order by day,start_date,process, end_date)
pivot(
    min(process)
    for Position
    IN (1 as positon_1,2 as position_2,3 as position_3,4 as position_4,5 as position_5,6 as position_6,7 as position_7)
)
group by day
order by day asc
;

Why isn't Process 3 on Position 1 on 05.01.2018 in your example?

DAY                 MIN(POSITON_1) MIN(POSITION_2) MIN(POSITION_3) MIN(POSITION_4) MIN(POSITION_5) MIN(POSITION_6) 
------------------- -------------- --------------- --------------- --------------- --------------- --------------- 
01.01.2018 00:00:00              1               2                                                                                 
02.01.2018 00:00:00              1               2                                                                                 
03.01.2018 00:00:00              1               3                                                                                 
04.01.2018 00:00:00              1               3                                                                                 
05.01.2018 00:00:00              3                                                                                                 
06.01.2018 00:00:00              3               4               5                                                                 
07.01.2018 00:00:00              3               4               5                                                                 
08.01.2018 00:00:00              3               4                                                                                 
09.01.2018 00:00:00              3               4               6                                                                 
10.01.2018 00:00:00              6                                                                                                 
11.01.2018 00:00:00              6               7                                                                                 
12.01.2018 00:00:00              6                                                                                                 
13.01.2018 00:00:00              6

Report message to a moderator

Re: Visualize Processes Vertical [message #665077 is a reply to message #665072]

Mon, 21 August 2017 08:24

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

SQL> SELECT  *
  2    FROM  PROCESSES
  3  /

   PROCESS START_DAT END_DATE
---------- --------- ---------
         1 01-JAN-18 05-JAN-18
         2 01-JAN-18 03-JAN-18
         3 03-JAN-18 10-JAN-18
         4 06-JAN-18 10-JAN-18
         5 06-JAN-18 08-JAN-18
         6 09-JAN-18 14-JAN-18
         7 11-JAN-18 12-JAN-18

7 rows selected.

SQL> WITH DATES AS (
  2                 SELECT  DATE '2017-12-31' + LEVEL DAY
  3                   FROM  DUAL
  4                   CONNECT BY LEVEL <= DATE '2018-01-20' - DATE '2017-12-31'
  5                ),
  6           T AS (
  7                 SELECT  D.DAY,
  8                         P.PROCESS,
  9                         ROW_NUMBER() OVER(PARTITION BY D.DAY ORDER BY P.PROCESS NULLS LAST) POSITION
 10                   FROM  DATES D,
 11                         PROCESSES P
 12                   WHERE D.DAY >= P.START_DATE(+)
 13                     AND D.DAY <  P.END_DATE(+)
 14                )
 15  SELECT  *
 16    FROM  T
 17    PIVOT(
 18          MAX(PROCESS)
 19          FOR POSITION IN (
 20                           1  AS POSITION_1,
 21                           2  AS POSITION_2,
 22                           3  AS POSITION_3,
 23                           4  AS POSITION_4,
 24                           5  AS POSITION_5,
 25                           6  AS POSITION_6,
 26                           7  AS POSITION_7,
 27                           8  AS POSITION_8,
 28                           9  AS POSITION_9,
 29                           10 AS POSITION_10
 30                          )
 31         )
 32    ORDER BY DAY
 33  /

DAY       POSITION_1 POSITION_2 POSITION_3 POSITION_4 POSITION_5 POSITION_6 POSITION_7 POSITION_8 POSITION_9 POSITION_10
--------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------
01-JAN-18          1          2
02-JAN-18          1          2
03-JAN-18          1          3
04-JAN-18          1          3
05-JAN-18          3
06-JAN-18          3          4          5
07-JAN-18          3          4          5
08-JAN-18          3          4
09-JAN-18          3          4          6
10-JAN-18          6
11-JAN-18          6          7
12-JAN-18          6
13-JAN-18          6
14-JAN-18
15-JAN-18
16-JAN-18
17-JAN-18
18-JAN-18
19-JAN-18
20-JAN-18

20 rows selected.

SQL>

SY.

Report message to a moderator

Re: Visualize Processes Vertical [message #665078 is a reply to message #665077]

Mon, 21 August 2017 08:46

ALEXWE
Messages: 11
Registered: August 2016
Location: Germany

Junior Member

Yours is cleaner SY.
--> Don't select process.start_Date,process.end_date (in my example)
and you don't need Group by and min(positon_1)

Report message to a moderator

Re: Visualize Processes Vertical [message #665079 is a reply to message #665076]

Mon, 21 August 2017 09:05

quirks
Messages: 82
Registered: October 2014

Member

Hello,

and thanks for your replys.

ALEXWE wrote on Mon, 21 August 2017 08:23

Why isn't Process 3 on Position 1 on 05.01.2018 in your example?

As soon as a process is allocated to a position it should stay on this position until it is finished. This is because switching processes between the different positions takes time (changeover times) and some tasks just can't be moved while they are executed (e.g. its prohibited to move parts while the glue is hardening).

Report message to a moderator

Re: Visualize Processes Vertical [message #665082 is a reply to message #665079]

Mon, 21 August 2017 09:29

ALEXWE
Messages: 11
Registered: August 2016
Location: Germany

Junior Member

SQL only or can PL/SQL be used?

Report message to a moderator

Re: Visualize Processes Vertical [message #665083 is a reply to message #665082]

Mon, 21 August 2017 09:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ALEXWE wrote on Mon, 21 August 2017 07:29

SQL only or can PL/SQL be used?

PL/SQL itself can NOT access the database; only SQL can.
Do not do in PL/SQL that which can be done in plain SQL

Report message to a moderator

Re: Visualize Processes Vertical [message #665084 is a reply to message #665082]

Mon, 21 August 2017 09:36

quirks
Messages: 82
Registered: October 2014

Member

It would be great in SQL because our business wants the query embeded in Excel ( Shocked

), but I think its possible to create a function and fetch the results via the TABLE() -function.

Report message to a moderator

Re: Visualize Processes Vertical [message #665085 is a reply to message #665084]

Mon, 21 August 2017 09:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

quirks wrote on Mon, 21 August 2017 07:36

It would be great in SQL because our business wants the query embeded in Excel ( ), but I think its possible to create a function and fetch the results via the TABLE() -function.

When properly configured, Excel can issue SQL directly against Oracle database.

You are free to abuse your database any way you deem appropriate.

Report message to a moderator

Re: Visualize Processes Vertical [message #665086 is a reply to message #665085]

Mon, 21 August 2017 10:25

quirks
Messages: 82
Registered: October 2014

Member

BlackSwan wrote on Mon, 21 August 2017 09:48

When properly configured, Excel can issue SQL directly against Oracle database.

Sadly I've to admit that I know how to create and use connections against an Oracle databases in excel.

But this discussion is now WAY off my initial request. Do you have an idea how to tackle my initial problem?

[Updated on: Mon, 21 August 2017 10:26]

Report message to a moderator

Re: Visualize Processes Vertical [message #665298 is a reply to message #665086]

Tue, 29 August 2017 06:19

quirks
Messages: 82
Registered: October 2014

Member

What I've been coming up so far:

WITH
    TEST_DATA -- the test data
    AS
        ((SELECT 1 PROCESS, TO_DATE('01.01.2018', 'DD.MM.YYYY') START_DATE, TO_DATE('05.01.2018', 'DD.MM.YYYY') END_DATE FROM DUAL)
         UNION ALL
         (SELECT 2, TO_DATE('01.01.2018', 'DD.MM.YYYY'), TO_DATE('03.01.2018', 'DD.MM.YYYY') FROM DUAL)
         UNION ALL
         (SELECT 3, TO_DATE('03.01.2018', 'DD.MM.YYYY'), TO_DATE('10.01.2018', 'DD.MM.YYYY') FROM DUAL)
         UNION ALL
         (SELECT 4, TO_DATE('06.01.2018', 'DD.MM.YYYY'), TO_DATE('10.01.2018', 'DD.MM.YYYY') FROM DUAL)
         UNION ALL
         (SELECT 5, TO_DATE('06.01.2018', 'DD.MM.YYYY'), TO_DATE('08.01.2018', 'DD.MM.YYYY') FROM DUAL)
         UNION ALL
         (SELECT 6, TO_DATE('09.01.2018', 'DD.MM.YYYY'), TO_DATE('14.01.2018', 'DD.MM.YYYY') FROM DUAL)
         UNION ALL
         (SELECT 7, TO_DATE('11.01.2018', 'DD.MM.YYYY'), TO_DATE('12.01.2018', 'DD.MM.YYYY') FROM DUAL)),
    DATES -- create a list of dates to join with the test data
    AS
        (SELECT DAY
           FROM (SELECT     TO_DATE('01.01.1901', 'DD.MM.YYYY') + ROWNUM - 1 AS DAY
                       FROM DUAL
                 CONNECT BY LEVEL <= TO_DATE('31.12.2999', 'DD.MM.YYYY') - TO_DATE('01.01.1901', 'DD.MM.YYYY') + 1)
          WHERE DAY BETWEEN TO_DATE('01.01.2018', 'DD.MM.YYYY') AND TO_DATE('20.01.2018', 'DD.MM.YYYY')),
    PROCESSES -- put together test data with dates and calculate the duration
    AS
        (SELECT DISTINCT PROCESS
                        ,END_DATE - START_DATE AS DURATION
                        ,DAY
                        ,START_DATE
                        ,END_DATE
           FROM TEST_DATA, DATES
          WHERE DATES.DAY BETWEEN TEST_DATA.START_DATE AND TEST_DATA.END_DATE),
    PROC_LVL1_RANKING -- calculate the rankig of the processes
    AS
        (SELECT PROCESS
               ,DURATION
               ,DAY
               ,START_DATE
               ,RANK() OVER(PARTITION BY DAY ORDER BY START_DATE, DURATION DESC, PROCESS) AS PROCESS_RANK
           FROM PROCESSES
          WHERE DAY <> END_DATE),
    PROC_LVL1_ORDER -- put processes with ranking 1 in the first slot
    AS
        (SELECT DISTINCT PROCESS
                        ,DURATION
                        ,DAY
                        ,START_DATE
                        ,CASE WHEN MAX(PROCESS_RANK) OVER (PARTITION BY PROCESS) = 1 THEN 1 ELSE 9999 END AS PROC_ORDER
           FROM PROC_LVL1_RANKING),
    PROC_LVL2_RANKING -- calculate the rankig of the remaining that are not ranked 1
    AS
        (SELECT DISTINCT PROCESS
                        ,DURATION
                        ,DAY
                        ,START_DATE
                        ,RANK() OVER(PARTITION BY DAY ORDER BY START_DATE, DURATION DESC, PROCESS) AS PROCESS_RANK
           FROM PROC_LVL1_ORDER
          WHERE PROC_ORDER = 9999),
    PROC_LVL2_ORDER -- put processes with ranking 1 in the second slot
    AS
        (SELECT DISTINCT PROCESS
                        ,DURATION
                        ,DAY
                        ,START_DATE
                        ,CASE WHEN MAX(PROCESS_RANK) OVER (PARTITION BY PROCESS) = 1 THEN 2 ELSE 9999 END AS PROC_ORDER
           FROM PROC_LVL2_RANKING),
    PROC_LVL3_RANKING -- calculate the rankig of the remaining that are not ranked 1,2
    AS
        (SELECT DISTINCT PROCESS
                        ,DURATION
                        ,DAY
                        ,START_DATE
                        ,RANK() OVER(PARTITION BY DAY ORDER BY START_DATE, DURATION DESC, PROCESS) AS PROCESS_RANK
           FROM PROC_LVL2_ORDER
          WHERE PROC_ORDER = 9999),
    PROC_LVL3_ORDER -- put processes with ranking 1 in the third slot
    AS
        (SELECT DISTINCT PROCESS
                        ,DURATION
                        ,DAY
                        ,START_DATE
                        ,CASE WHEN MAX(PROCESS_RANK) OVER (PARTITION BY PROCESS) = 1 THEN 3 ELSE 9999 END AS PROC_ORDER
           FROM PROC_LVL3_RANKING),
    PROC_LVL4_RANKING -- calculate the rankig of the remaining that are not ranked 1,2,3
    AS
        (SELECT DISTINCT PROCESS
                        ,DURATION
                        ,DAY
                        ,START_DATE
                        ,RANK() OVER(PARTITION BY DAY ORDER BY START_DATE, DURATION DESC, PROCESS) AS PROCESS_RANK
           FROM PROC_LVL3_ORDER
          WHERE PROC_ORDER = 9999),
    PROC_LVL4_ORDER -- and so on
    AS
        (SELECT DISTINCT PROCESS
                        ,DURATION
                        ,DAY
                        ,START_DATE
                        ,CASE WHEN MAX(PROCESS_RANK) OVER (PARTITION BY PROCESS) = 1 THEN 4 ELSE 9999 END AS PROC_ORDER
           FROM PROC_LVL4_RANKING),
    PROC_LVL5_RANKING -- and on 
    AS
        (SELECT DISTINCT PROCESS
                        ,DURATION
                        ,DAY
                        ,START_DATE
                        ,RANK() OVER(PARTITION BY DAY ORDER BY START_DATE, DURATION DESC, PROCESS) AS PROCESS_RANK
           FROM PROC_LVL4_ORDER
          WHERE PROC_ORDER = 9999),
    PROC_LVL5_ORDER
    AS
        (SELECT DISTINCT PROCESS
                        ,DURATION
                        ,DAY
                        ,START_DATE
                        ,CASE WHEN MAX(PROCESS_RANK) OVER (PARTITION BY PROCESS) = 1 THEN 5 ELSE 9999 END AS PROC_ORDER
           FROM PROC_LVL5_RANKING),
    PROC_LVL6_RANKING
    AS
        (SELECT DISTINCT PROCESS
                        ,DURATION
                        ,DAY
                        ,START_DATE
                        ,RANK() OVER(PARTITION BY DAY ORDER BY START_DATE, DURATION DESC, PROCESS) AS PROCESS_RANK
           FROM PROC_LVL5_ORDER
          WHERE PROC_ORDER = 9999),
    PROC_LVL6_ORDER
    AS
        (SELECT DISTINCT PROCESS
                        ,DURATION
                        ,DAY
                        ,START_DATE
                        ,CASE WHEN MAX(PROCESS_RANK) OVER (PARTITION BY PROCESS) = 1 THEN 5 ELSE 9999 END AS PROC_ORDER
           FROM PROC_LVL6_RANKING),
    TOGETHER
    AS
        (SELECT PROCESS, DAY, PROC_ORDER
           FROM PROC_LVL1_ORDER
          WHERE PROC_ORDER <> 9999
         UNION ALL
         SELECT PROCESS, DAY, PROC_ORDER
           FROM PROC_LVL2_ORDER
          WHERE PROC_ORDER <> 9999
         UNION ALL
         SELECT PROCESS, DAY, PROC_ORDER
           FROM PROC_LVL3_ORDER
          WHERE PROC_ORDER <> 9999
         UNION ALL
         SELECT PROCESS, DAY, PROC_ORDER
           FROM PROC_LVL4_ORDER
          WHERE PROC_ORDER <> 9999
         UNION ALL
         SELECT PROCESS, DAY, PROC_ORDER
           FROM PROC_LVL5_ORDER
          WHERE PROC_ORDER <> 9999
         UNION ALL
         SELECT PROCESS, DAY, PROC_ORDER
           FROM PROC_LVL6_ORDER
          -- WHERE PROC_ORDER <> 9999  -- by not filtering out the PROC_ORDER <> 9999 I can show them in the NOT_ALLOCATED slot
         )
(SELECT *
   FROM TOGETHER
        PIVOT
            (MAX(PROCESS)
            FOR PROC_ORDER
            IN (1 AS "Position_1"
              ,2 AS "Position_2"
              ,3 AS "Position_3"
              ,4 AS "Position_4"
              ,5 AS "Position_5"
              ,6 AS "Position_6"
              ,9999 AS "NOT_ALLOCATED")))
ORDER BY DAY;

At least the processes don't overwrite each other and stay in their slots.

I'd be glad if someone could show me how to improve my attempt.

[Updated on: Tue, 29 August 2017 06:20]

Report message to a moderator

Previous Topic:	Oracle lookup function in loop returns rowtype how to get fields from rowtype
Next Topic:	Invalid Datatype error for record type

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 19:04:38 CDT 2024