Thanks for the question.

Asked: August 23, 2017 - 10:27 am UTC

Last updated: September 10, 2017 - 1:05 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hello,

I've got a tricky one and can't solve it. Hopefully you 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:

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

Here is the create script:

CREATE TABLE TEST_PROJ(
    PROCESS_ID NUMBER
   ,START_DATE DATE
   ,END_DATE DATE
);


INSERT INTO TEST_PROJ(PROCESS_ID, 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_ID, 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_ID, 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_ID, 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_ID, 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_ID, 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_ID, 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 on a daily basis. The order of the positions is determined by its START_DATE, its duration DESCENDING, and at last by its PROCESS_ID. When a process has chosen its position, then it must keep it until it is finished even if a position slot in front of it becomes free. This free slot can then be occupied by an other process. Here is an example of the desired output:

DAY        |Position_1 |Position_2 |Position_3 |Position_4 |Position_5 
-----------+-----------+-----------+-----------+-----------+-----------
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 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'.

What I've come up with is the following statement. The result seems to be fine at the first glance but if you look at day '06.01.2018' you'll notice that process 4 overwrites process 3. I understand why my SQL behaves in that way, but I have no idea how to get the desired output. I'll be glad for every hint how to tackle this problem.

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_ID
                        ,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_ID
               ,DURATION
               ,DAY
               ,START_DATE
               ,END_DATE
               ,RANK() OVER(PARTITION BY DAY ORDER BY START_DATE, DURATION DESC, PROCESS_ID) AS PROCESS_RANK
           FROM PROCESSES
          WHERE DAY <> END_DATE),
    PROC_ORDER
    AS
        (SELECT DISTINCT PROC_RANKING.PROCESS_ID, MAX(PROC_RANKING.PROCESS_RANK) OVER (PARTITION BY PROC_RANKING.PROCESS_ID) AS PROCES_POSITION
           FROM PROC_RANKING),
    TOGETHER
    AS
        (SELECT PROCESSES.PROCESS_ID, PROCESSES.DAY, PROC_ORDER.PROCES_POSITION
           FROM PROCESSES, PROC_ORDER
          WHERE PROCESSES.PROCESS_ID = PROC_ORDER.PROCESS_ID AND PROCESSES.DAY <> PROCESSES.END_DATE)
(SELECT *
   FROM TOGETHER
        PIVOT
            (MAX(TOGETHER.PROCESS_ID)
            FOR PROCES_POSITION
            IN (1 AS "Position_1"
              ,2 AS "Position_2"
              ,3 AS "Position_3"
              ,4 AS "Position_4"
              ,5 AS "Position_5")))
ORDER BY DAY;

and Chris said...

The basic problem here is that the current position of a process is taken from its position on the previous day. If it existed.

The PROC_RANKING step only considers relative positions on the current day.

What you need is a recursive operation. After the first day, this assigns processes to slots by:

- First filling the slots for the processes that existed yesterday & today
- Then filling the empty slots based on the "ideal" rank for that day

If there is a way to do this in pure SQL, I can't find it!

But the following PL/SQL procedure will do it. Pass it comma-separated strings of the previous assignment and the "ideal" current assignment.

The function splits these into comma-separated arrays. One of these is indexed by the processes for the current day. You can then loop through the previous day's array to see if that process exists on the current day.

If it does, assign it to the previous day's slot and remove it from the list of current processes to add.

Once you've complete this loop, you can loop through the assigned slots. For any that are empty, take the first process from the current slots (if there is one). Then remove it from the current list.

Finally convert the actual assignments back to a comma separated list and return that.

Which gives:

create or replace function f ( prev_seq varchar2, curr_seq varchar2 ) 
  return varchar2 as
  
  prev_vals_tab  dbms_sql.number_table;
  curr_vals_tab  dbms_sql.number_table;
  curr_inds_tab  dbms_sql.number_table;
  final_vals_tab dbms_sql.number_table;
  
  ith_curr_value pls_integer;
  ith_prev_value pls_integer;
  
  final_seq varchar2(30);
begin
  -- parse strings into tables
  for i in 1 .. 5 loop
    if i = 1 then
      ith_curr_value := substr ( curr_seq, 1, instr(curr_seq, ',') - 1 );
      ith_prev_value := substr ( prev_seq, 1, instr(prev_seq, ',') - 1 );

    elsif i = 5 then
      ith_curr_value := substr ( curr_seq, instr(curr_seq, ',', 1, 4) + 1);
      ith_prev_value := substr ( prev_seq, instr(prev_seq, ',', 1, 4) + 1);

    else
      ith_curr_value := substr ( curr_seq, instr(curr_seq, ',', 1, i-1) + 1, instr(curr_seq, ',', 1, i) - instr(curr_seq, ',', 1, i-1) - 1 );
      ith_prev_value := substr ( prev_seq, instr(prev_seq, ',', 1, i-1) + 1, instr(prev_seq, ',', 1, i) - instr(prev_seq, ',', 1, i-1) - 1 );

    end if;
    
    prev_vals_tab(i) := ith_prev_value;
    curr_vals_tab(i) := ith_curr_value;
    if ith_curr_value is not null then 
      curr_inds_tab( ith_curr_value ) := i ;
    end if;
  end loop;
  /* If a process is in the current and previous days
     - assign it to the same slot it had in the previous day
        & remove from the current list
  */
  for i in 1 .. 5 loop
    if curr_inds_tab.exists(prev_vals_tab(i)) then
      final_vals_tab(i) := prev_vals_tab(i);
      curr_vals_tab.delete(curr_inds_tab(prev_vals_tab(i)));
    end if;
  end loop;
  
  /* For the unassigned slots:
     - Take the first value from the current list (and remove it)
  */
  for i in 1 .. 5 loop
    if not final_vals_tab.exists(i) then
      final_vals_tab(i) := curr_vals_tab(curr_vals_tab.first);
      curr_vals_tab.delete(curr_vals_tab.first);
    end if;
  end loop;

  -- convert array to comma separated list
  for i in 1 .. 5 loop
    if final_vals_tab.exists(i) then 
      final_seq := final_seq || final_vals_tab(i);
    end if;
    if i < 5 then 
      final_seq := final_seq || ',' ;
    end if;
  end loop;
  
  return final_seq ;
end f;
/

With this in place, it's time for the SQL!

Generate all the desired days and slots. Assign processes to their "ideal" slot (not considering previous assignment).

Then collapse this down to a single row for each day with listagg to get a comma separated list of processes each day.

You can now start recursing through this. For each day, pass the previous day's process assignments and the current "ideal" arrangement.

The function will spit out the correct order. Keep going until you've generated all the days you need:

WITH DATES AS (
  SELECT  TO_DATE('01.01.2018', 'DD.MM.YYYY') + ROWNUM - 1 AS DAY, 
          TO_DATE('01.01.2018', 'DD.MM.YYYY') first_day
  FROM DUAL
  CONNECT BY LEVEL <= TO_DATE('20.01.2018', 'DD.MM.YYYY') - TO_DATE('01.01.2018', 'DD.MM.YYYY') + 1
), slots as (
  select level slot_no 
  from   dual connect by level <= 5
), date_slots as (
  select * from dates
  cross join slots
), proc_days as (
  select dt.day, tp.process_id, 
         row_number() over (partition by dt.day order by tp.process_id) rn
  from   dates dt
  left join test_proj tp
  on     dt.day >= tp.start_date
  and    dt.day < tp.end_date
), proc_slots as (
  select ds.day, ds.first_day, 
         replace(listagg(coalesce(to_char(pd.process_id), '##'), ',') within group (order by ds.slot_no), '##') slots
  from   date_slots ds
  left join proc_days pd
  on     ds.day = pd.day
  and    ds.slot_no = pd.rn
  group  by ds.day, ds.first_day
), day_orders (dy, curr_slots, prev_slots) as (
  select ps.day dy, ps.slots, ',,,,'
  from   proc_slots ps
  where  ps.day = ps.first_day
  union all
  select ps.day,
         f(do.curr_slots, ps.slots ), do.curr_slots
  from   day_orders do
  join   proc_slots ps
  on     do.dy + 1 = ps.day
)
  select dy, curr_slots
  from   day_orders
  order  by dy;

DY                    CURR_SLOTS  
01-JAN-2018 00:00:00  1,2,,,      
02-JAN-2018 00:00:00  1,2,,,      
03-JAN-2018 00:00:00  1,3,,,      
04-JAN-2018 00:00:00  1,3,,,      
05-JAN-2018 00:00:00  ,3,,,       
06-JAN-2018 00:00:00  4,3,5,,     
07-JAN-2018 00:00:00  4,3,5,,     
08-JAN-2018 00:00:00  4,3,,,      
09-JAN-2018 00:00:00  4,3,6,,     
10-JAN-2018 00:00:00  ,,6,,       
11-JAN-2018 00:00:00  7,,6,,      
12-JAN-2018 00:00:00  ,,6,,       
13-JAN-2018 00:00:00  ,,6,,       
14-JAN-2018 00:00:00  ,,,,        
15-JAN-2018 00:00:00  ,,,,        
16-JAN-2018 00:00:00  ,,,,        
17-JAN-2018 00:00:00  ,,,,        
18-JAN-2018 00:00:00  ,,,,        
19-JAN-2018 00:00:00  ,,,,        
20-JAN-2018 00:00:00  ,,,,

Phew! That was tough! ;)

All you have to do now is split the comma-separated strings out into columns :)

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Know in advance the number of slots

Stew Ashton, September 07, 2017 - 9:44 pm UTC

In order to get output in columns the way you want, you have to tell SQL how many columns you want at the start. In other words, you have to hard-code the number, datatype and order of the columns to be produced by the query.

Once you accept that limitation, you can hard-code a solution to fit the output columns you decided on. For example, suppose you decide that there will never be more than four positions. Then:

select * from (
  select dte, pos, process_id
  from  (
    select process_id, start_date, end_date, end_date - start_date diff_date 
    from test_proj
  )
  match_recognize(
    order by start_date, diff_date desc, process_id
    measures ascii(classifier()) - ascii('A') + 1 pos
    all rows per match
    pattern( (a|b|c|d)+ )
    define a as count(a.*) = 1 or start_date >= last(a.end_date,1),
           b as count(b.*) = 1 or start_date >= last(b.end_date,1),
           c as count(c.*) = 1 or start_date >= last(c.end_date,1),
           d as count(d.*) = 1 or start_date >= last(d.end_date,1)
  )
  ,
  lateral(
    select start_date-1+level dte from dual 
    connect by start_date-1+level < end_date
  )
)
pivot(max(process_id) for pos in (1,2,3,4))
order by dte;

DTE                 1          2          3          4
---------- ---------- ---------- ---------- ----------
2018-01-01          1          2                      
2018-01-02          1          2                      
2018-01-03          1          3                      
2018-01-04          1          3                      
2018-01-05                     3                      
2018-01-06          4          3          5           
2018-01-07          4          3          5           
2018-01-08          4          3                      
2018-01-09          4          3          6           
2018-01-10                                6           
2018-01-11          7                     6           
2018-01-12                                6           
2018-01-13                                6

Now suppose you want a solution that will handle any number of positions. Then you have the same problem as with any use of PIVOT: you have to dynamically generate the SQL according to the number of columns needed, then execute the generated SQL.

Do you need that dynamic solution, or is it enough to hard-code a maximum number of columns as I have done?

Best regards, Stew Ashton

September 10, 2017 - 12:54 pm UTC

Nicely done Stew, I suspected match_recognize would form a solution ;)

Just because I started with a recursive CTE

Andrew Sayer, September 08, 2017 - 12:45 am UTC

Of course, Stew posted a very neat solution with the model clause. It took me a couple of hours of perseverance but it's also possible with a recursive CTE but I did need to use cross apply (need 12c) . Coupled with a bit of pivot and unpivot


with ranges as (select (select min(start_date) min_date from test_proj) min_date
                      ,(select max(end_date)   max_date from test_proj) max_date
                from   dual
               )
    ,a_day ( day_of , "1_PROCESS", "1_DATE"
                    , "2_PROCESS", "2_DATE"
                    , "3_PROCESS", "3_DATE"
                    , "4_PROCESS", "4_DATE"
                    , "5_PROCESS", "5_DATE"
                   ) 
    as 
                 (select day_of , "1_PROCESS", "1_DATE"
                    , "2_PROCESS", "2_DATE"
                    , "3_PROCESS", "3_DATE"
                    , "4_PROCESS", "4_DATE"
                    , "5_PROCESS", "5_DATE"
                  from (
                  select ranges.min_date day_of, tp.process_id
                        ,tp.end_date
                        ,row_number() over (partition by null order by tp.start_date, tp.end_date-tp.start_date desc, tp.process_id) rown 
                  from   ranges
                  join   test_proj tp 
                    on   tp.start_date = ranges.min_date)
                  pivot (max(process_id) as "PROCESS",max(end_date) as "DATE"
                         for rown in (1 
                                     ,2 
                                     ,3 
                                     ,4 
                                     ,5
                                     )
                        )
               union all
                  select ad.day_of+1
                        ,ca."1_PROCESS", ca."1_DATE"
                        ,ca."2_PROCESS", ca."2_DATE"
                        ,ca."3_PROCESS", ca."3_DATE"
                        ,ca."4_PROCESS", ca."4_DATE"
                        ,ca."5_PROCESS", ca."5_DATE"
                  from   a_day ad
                  cross apply (
                  select 
                      "1_PROCESS", "1_DATE"
                    , "2_PROCESS", "2_DATE"
                    , "3_PROCESS", "3_DATE"
                    , "4_PROCESS", "4_DATE"
                    , "5_PROCESS", "5_DATE"
                  from (select process_id 
                              ,end_date
                              ,decode(tag,1,rown,row_number() over (partition by null order by rn,tag)) rown
                        from (
                        select process_id
                              ,end_date
                              ,tag
                              ,rown
                              ,rown + decode(tag,1,0,sum(decode(tag,1,1,0)) over (partition by null order by rown, tag)) rn
                        from  (select process_id
                                     ,end_date
                                     ,1 tag
                                     ,rown
                               from (               
                               select ad.day_of+1 day_of
                                    , ad."1_PROCESS", ad."1_DATE"
                                    , ad."2_PROCESS", ad."2_DATE"
                                    , ad."3_PROCESS", ad."3_DATE"
                                    , ad."4_PROCESS", ad."4_DATE"
                                    , ad."5_PROCESS", ad."5_DATE"
                               from   dual
                               )
                               unpivot ((process_id, end_date)
                                        for rown in (("1_PROCESS","1_DATE") as 1
                                                    ,("2_PROCESS","2_DATE") as 2
                                                    ,("3_PROCESS","3_DATE") as 3
                                                    ,("4_PROCESS","4_DATE") as 4
                                                    ,("5_PROCESS","5_DATE") as 5
                                                    )
                                       )
                               where    end_date > ad.day_of+1
                             union all
                               select tp.process_id
                                     ,tp.end_date
                                     ,2 tag
                                     ,row_number() over (partition by null order by tp.start_date, tp.end_date-tp.start_date desc, tp.process_id) rown 
                               from   test_proj tp 
                               where  tp.start_date = ad.day_of+1
                              )
                              )
                       )
                  pivot (max(process_id) as "PROCESS",max(end_date) as "DATE"
                         for rown in (1 
                                     ,2 
                                     ,3 
                                     ,4 
                                     ,5
                                     )
                        )
                        ) ca
                  where  ad.day_of+1 < (select max_date from ranges)
                 ) 
                 cycle day_of set is_cycle to '1' default '0'
select day_of
      ,"1_PROCESS" "Position 1"
      ,"2_PROCESS" "Position 2"
      ,"3_PROCESS" "Position 3"
      ,"4_PROCESS" "Position 4"
      ,"5_PROCESS" "Position 5"
from   a_day;


DAY_OF              Position 1 Position 2 Position 3 Position 4 Position 5
------------------- ---------- ---------- ---------- ---------- ----------
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          4          3          5
07/01/2018 00:00:00          4          3          5
08/01/2018 00:00:00          4          3
09/01/2018 00:00:00          4          3          6
10/01/2018 00:00:00                                6
11/01/2018 00:00:00          7                     6
12/01/2018 00:00:00                                6
13/01/2018 00:00:00                                6

13 rows selected.

Time to sleep now I think!

September 10, 2017 - 12:56 pm UTC

MODEL Clause generic solution

Brendan, September 08, 2017 - 10:57 am UTC

This distracted me from my blog post :)

The idea behind this one is that if you have assigned slots 1...n-1 then slot n is assigned sequentially to the remaining non-overlapping records. I managed to implement it (not without some difficulty) using Model clause. This should work for any number of slots upto the iteration limit specified.

I did not implement the displaying per day and pivoting for a fixed number of slots, which could be done similarly to Stew's solution for example.

PROCESS_ID START_DAT END_DATE        SLOT    IS_DONE       ITER
---------- --------- --------- ---------- ---------- ----------
         1 01-JAN-18 05-JAN-18          1          0          3
         2 01-JAN-18 03-JAN-18          2          0          3
         3 03-JAN-18 10-JAN-18          2          0          3
         4 06-JAN-18 10-JAN-18          1          0          3
         5 06-JAN-18 08-JAN-18          3          0          3
         6 09-JAN-18 14-JAN-18          3          0          3
         7 11-JAN-18 12-JAN-18          1          0          3

7 rows selected.

  1  SELECT process_id, start_date, end_date, slot, is_done, iter
  2    FROM test_proj
  3   MODEL
  4      DIMENSION BY (Row_Number() OVER (ORDER BY start_date, end_date - start_date DESC, process_id) rn)
  5      MEASURES (process_id, start_date, end_date, CAST(NULL AS NUMBER) slot, CAST(NULL AS NUMBER) is_done, 0 iter)
  6      RULES ITERATE (1000000) UNTIL is_done[1] = 0 (
  7         iter[rn] = iteration_number+1,
  8         slot[rn] = Nvl (slot[CV()], CASE WHEN start_date[CV()] >= Nvl (Max (CASE WHEN slot = iter THEN end_date END)[rn<CV()], DATE '1900-01-01') THEN iter[CV()] END),
  9         is_done[rn] = Count(Nvl2(slot, NULL, 1))[ANY]
 10*     )

September 10, 2017 - 12:59 pm UTC

Great work Brendan, the only pre-12c solution we have so far :)

Excellent use of MODEL clause

Stew Ashton, September 08, 2017 - 7:41 pm UTC

I generally avoid the MODEL clause with ITERATE for performance and complexity reasons, but Brendan's solution forces my respect.

Leaving aside the pivoting as Brendan does, but working with any number of positions, this is the best I can do with MATCH_RECOGNIZE. Starting from every row in turn, I find those rows that could go in the same position as the starting row. Then for each row I find the earliest possible starting row.

select process_id, start_date, end_date,
dense_rank() over(order by decode(rn,1,mn)) pos
from (
  select process_id, start_date, end_date, mn,
  row_number() over(partition by process_id order by mn) rn
  from (
    select a.*, end_date - start_date diff_date from test_proj a
  )
  match_recognize(
    order by start_date, diff_date desc, process_id
    measures match_number() mn
    all rows per match
    after match skip to next row
    pattern ( (y|{-n-})+ )
    define y as count(y.*) = 1 or y.start_date >= last(y.end_date,1)
  ) m
)
where rn = 1
order by pos, start_date;

PROCESS_ID START_DATE END_DATE          POS
---------- ---------- ---------- ----------
         1 2018-01-01 2018-01-05          1
         4 2018-01-06 2018-01-10          1
         7 2018-01-11 2018-01-12          1
         2 2018-01-01 2018-01-03          2
         3 2018-01-03 2018-01-10          2
         5 2018-01-06 2018-01-08          3
         6 2018-01-09 2018-01-14          3

Best regards, Stew

September 10, 2017 - 1:05 pm UTC

Nice work on a generic pattern matching way :)

I agree model generally isn't worth it. Looks like the way to go if you need a pre-12c solution though.

MODEL without ITERATE

Lukas Eder, September 10, 2017 - 7:03 pm UTC

While wasting about 1 hour to trying to tackle an elegant and concise recursive solution, I gave up and applied MODEL. Here's a version that does not use ITERATE

SELECT *
FROM (
  SELECT day, process_id, p
  FROM test_proj t
  CROSS APPLY (
    SELECT start_date + LEVEL - 1 day
    FROM dual
    CONNECT BY start_date + LEVEL - 1 < end_date
  ) u
  CROSS JOIN (SELECT 0 p FROM dual)
  MODEL
    DIMENSION BY (row_number() OVER (ORDER BY start_date, process_id, day) AS rn, day)
    MEASURES (process_id, p)
    RULES (
      p[any, any] = COALESCE(
        p[cv(rn) - 1, cv(day) - 1],
        CASE 
          WHEN COUNT(CASE WHEN p = 1 THEN 1 END)[rn BETWEEN 1 AND cv(rn) - 1, day = cv(day)] = 0 THEN 1 
          WHEN COUNT(CASE WHEN p = 2 THEN 1 END)[rn BETWEEN 1 AND cv(rn) - 1, day = cv(day)] = 0 THEN 2 
          WHEN COUNT(CASE WHEN p = 3 THEN 1 END)[rn BETWEEN 1 AND cv(rn) - 1, day = cv(day)] = 0 THEN 3 
          WHEN COUNT(CASE WHEN p = 4 THEN 1 END)[rn BETWEEN 1 AND cv(rn) - 1, day = cv(day)] = 0 THEN 4 
        END
      )
    )
)
PIVOT (
  MAX(process_id) FOR p IN (1, 2, 3, 4)
)
ORDER BY day;

It hard wires the number of accepted positions two times:

- Once in the RULES clause
- Once in the PIVOT clause

I guess that's acceptable, though.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Know in advance the number of slots

Just because I started with a recursive CTE

MODEL Clause generic solution

Excellent use of MODEL clause

MODEL without ITERATE

More to Explore

Analytics

Visualize Processes Vertical

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Know in advance the number of slots

Just because I started with a recursive CTE

MODEL Clause generic solution

Excellent use of MODEL clause

MODEL without ITERATE

More to Explore

Analytics