Home » SQL & PL/SQL » SQL & PL/SQL » Visualize Processes Vertical (Oracle 12)
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 SpoilerCREATE TABLE TEST_PROJ(
PROCESS NUMBER
,START_DATE DATE
,END_DATE DATE
);
INSERT INTO TEST_PROJ(PROCESS, START_DATE, END_DATE)
VALUES (1, TO_DATE('01.01.2018', 'DD.MM.YYYY'), TO_DATE('05.01.2018', 'DD.MM.YYYY'));
INSERT INTO TEST_PROJ(PROCESS, START_DATE, END_DATE)
VALUES (2, TO_DATE('01.01.2018', 'DD.MM.YYYY'), TO_DATE('03.01.2018', 'DD.MM.YYYY'));
INSERT INTO TEST_PROJ(PROCESS, START_DATE, END_DATE)
VALUES (3, TO_DATE('03.01.2018', 'DD.MM.YYYY'), TO_DATE('10.01.2018', 'DD.MM.YYYY'));
INSERT INTO TEST_PROJ(PROCESS, START_DATE, END_DATE)
VALUES (4, TO_DATE('06.01.2018', 'DD.MM.YYYY'), TO_DATE('10.01.2018', 'DD.MM.YYYY'));
INSERT INTO TEST_PROJ(PROCESS, START_DATE, END_DATE)
VALUES (5, TO_DATE('06.01.2018', 'DD.MM.YYYY'), TO_DATE('08.01.2018', 'DD.MM.YYYY'));
INSERT INTO TEST_PROJ(PROCESS, START_DATE, END_DATE)
VALUES (6, TO_DATE('09.01.2018', 'DD.MM.YYYY'), TO_DATE('14.01.2018', 'DD.MM.YYYY'));
INSERT INTO TEST_PROJ(PROCESS, START_DATE, END_DATE)
VALUES (7, TO_DATE('11.01.2018', 'DD.MM.YYYY'), TO_DATE('12.01.2018', 'DD.MM.YYYY'));
COMMIT;
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
|
|
|
Re: Visualize Processes Vertical [message #665077 is a reply to message #665072] |
Mon, 21 August 2017 08:24 |
Solomon Yakobson
Messages: 3286 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.
|
|
|
|
|
|
|
|
|
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:48When 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
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:51:52 CDT 2024
|