14 Replies Latest reply: Dec 3, 2012 4:41 PM by Peter vd Zwan RSS

    Seat Distribution-Can we do this in SQL?

    887479
      Hi,

      11gR2 DB

      Below is the table structure and data (Test tables and data)
      create table seat_master(grade varchar2(1),balance number);
      
      insert into seat_master values('A',3);
      insert into seat_master values('B',2);
      insert into seat_master values('C',1);
      insert into seat_master values('D',1);
      insert into seat_master values('E',6);
      
      create table seat_reservation(res_id varchar2(5),grade varchar2(1),
                  res_count number,group_flag varchar2(1));
      
      insert into seat_reservation values('R1','A',2,'Y');
      insert into seat_reservation values('R2','B',1,'Y');
      insert into seat_reservation values('R3','A',2,'Y');
      insert into seat_reservation values('R4','C',2,'N');
      insert into seat_reservation values('R5','E',3,'N');
      insert into seat_reservation values('R6','D',2,'N');
      
      select *
      from seat_master;
      
      GRADE BALANCE
      ----- -------
      A           3 
      B           2 
      C           1 
      D           1 
      E           6 
      
      
      select *
      from seat_reservation;
      
      RES_ID GRADE RES_COUNT GROUP_FLAG
      ------ ----- --------- ----------
      R1     A             2 Y          
      R2     B             1 Y          
      R3     A             2 Y          
      R4     C             2 N          
      R5     E             3 N          
      R6     D             2 N          
      
       6 rows selected  
      Now the objective is to distribute the seats according to the reservation.

      Rules:
      Seat distribution is done as per the order of RES_ID (ORDER BY RES_ID)
      A is the highest grade and E is the lowest.
      For someone booked for A, and if balance is not avilable for A he will be given B (Then C,D,E)
      For someone booked for B, and if balance is not avilable for A he will be given C (Then D,E), but highr grade wont be given
      GROUP_FLAG means that reservation should be in same grade -
           For example R1, is for 2 seats in A. They will not accept 1 seat in A and 1 Seat in B.Both the two seats should be either in A or in B or in C...
      And for a reservation, seats should be allocated only if all the requestd count is available.ie if requested count is 2 and only one is available in the particular grade or below it cannot be awarded

      Here the expected output:
      RES_ID SEAT 
      ------ ----
      R1          A1 
      R1          A2--Two A grade seats for R1, as R1 has RES_COUNT-2
             
      R2     B1
      
      R3          E1--R3 is given E grade, bcause as per now 2 seats 
      R3          E2--are not available in A or B or C.And GroupFlag is Y for him
      
      R4          C1--Only one seat availabl in C grade, so next seat will be Given
      R4          D1--in lower available grade
      
      R5          E3
      R5          E4
      R5          E5
      R6            --Blank as only one seat can be awarded
      Currently this is done in PL/SQL, can we do this in SQL?

      Thanks.

      Edited by: 884476 on Nov 18, 2012 12:14 AM
        • 1. Re: Seat Distribution-Can we do this in SQL?
          Hoek
          Most interesting and challenging question!
          Thanks for posting a testcase as well.

          Now I'm pretty sure this could be done in SQL and therefore this question deserves a 'bump', as it's open/unanswered for a week now, I just noticed. No hijacking intended.
          Perhaps it's a piece of cake for someone experienced with the MODEL clause or some analytical or recursion or...?
          Below is what I tried sofar.
          Part I'm currently struggling on is moving R6's D1-seat up to R4.
          It's sort of kind of 'processing sets in multiple ordered windows'...
          (Does anyone know a better/shorter description in English?)
          SQL> with reservations as ( select res_id
            2                         ,      grade
            3                         ,      res_count
            4                         ,      group_flag
            5                         ,      row_number() over (partition by grade order by res_id) rn
            6                         from   seat_reservation
            7                         connect by level <= res_count
            8                                and res_id = prior res_id
            9                                and prior dbms_random.value is not null
           10                       )
           11  select res_id
           12  ,      case
           13           when (
           14                  ( seat_no > 0 and  group_flag = 'Y' )
           15                or
           16                  ( seat_no = 0 and  group_flag = 'N' )
           17                )
           18           then grade||rn
           19           else
           20           case
           21             when ( seat_no <= 0 and group_flag = 'Y' )
           22             then ( select r2.grade
           23                    from   seat_master r2
           24                    where  r2.balance - rn >= res_count
           25                  )||to_char((rn-balance)+1)
           26             when ( seat_no > 0 and group_flag = 'N' )
           27             then ( select r2.grade
           28                    from   seat_master r2
           29                    where  r2.balance - rn >= res_count
           30                  )||to_char((balance-rn)) 
           31           end
           32         end seat
           33  from ( select r.res_id
           34         ,      r.res_count
           35         ,      r.group_flag
           36         ,      r.rn
           37         ,      m.grade
           38         ,      m.balance
           39         ,      (m.balance - r.rn) seat_no
           40         from   reservations r
           41         ,      seat_master m
           42         where  r.grade = m.grade
           43         order by r.res_id
           44       );
          
          RES_I SEAT
          ----- -----------------------------------------
          R1    A1
          R1    A2
          R2    B1
          R3    E1
          R3    E2
          R4    C1
          R4         <--? 
          R5    E5      |
          R5    E4      |
          R5    E3      |
          R6    D1    >-|
          R6
          
          12 rows selected.
          • 2. Re: Seat Distribution-Can we do this in SQL?
            Peter vd Zwan
            Hi,

            As Hoek stated it can be done with the model clause.

            Try like this:
            WITH BAL AS
            (select
              SM.*
              ,ROW_NUMBER() OVER (ORDER BY SM.GRADE) GRADE_NO
            
            from
              seat_master   SM
            UNION ALL
            SELECT
              '0'
              ,0
              ,0
            FROM
              DUAL
            )
            ,SR AS
            (
            SELECT
              SR.*
              ,ROW_NUMBER() OVER (ORDER BY RES_ID) RES_NO
              ,(SELECT GRADE_NO FROM BAL WHERE GRADE = SR.GRADE) RES_GRADE_NO
            FROM
              seat_reservation SR
            )
            
            ,MOD_IN AS
            (
            SELECT
              SR.RES_ID
              ,RES_NO
              ,BAL.GRADE
              ,BAL.GRADE_NO
              ,BAL.BALANCE
              ,SR.GRADE    RES_GRADE
              ,SR.RES_GRADE_NO
              ,CASE WHEN BAL.GRADE_NO = 0 THEN SR.RES_COUNT ELSE 0 END RES_COUNT
              ,SR.GROUP_FLAG
            
            FROM
              SR
              ,BAL
            )
            
            ,MDL AS
            (
            SELECT
              *
            
            FROM
              MOD_IN
            MODEL
            DIMENSION BY (RES_NO, GRADE_NO)
            MEASURES ( RES_ID, GRADE, BALANCE,RES_GRADE, RES_GRADE_NO, RES_COUNT, GROUP_FLAG GF)
            RULES
            SEQUENTIAL ORDER
            ITERATE(10) UNTIL (RES_ID[ITERATION_NUMBER + 1,0] IS NULL)
            (
            RES_COUNT[ITERATION_NUMBER + 1, FOR GRADE_NO FROM 1 TO 5 INCREMENT 1] =
                    CASE WHEN GF[CV(),CV()] = 'Y' 
                              AND MAX(RES_COUNT) [CV(), GRADE_NO > 0] = 0
                              AND BALANCE[CV(), CV()] >= RES_COUNT[CV(),0]
                              AND RES_GRADE[CV(), CV()] <= GRADE[CV(),CV()]
                         THEN RES_COUNT[ITERATION_NUMBER + 1,0] 
                
                              WHEN GF[CV(),CV()] = 'N' 
                              AND SUM(RES_COUNT) [CV(), GRADE_NO > 0] < RES_COUNT[ITERATION_NUMBER + 1,0]
                              AND SUM(BALANCE) [CV(), GRADE_NO >= RES_GRADE_NO[CV(),CV()] ] >= RES_COUNT[CV(),0]
                              AND RES_GRADE[CV(), CV()] <= GRADE[CV(),CV()]
                         THEN LEAST(RES_COUNT[ITERATION_NUMBER + 1,0], BALANCE[CV(), CV()] )
                         ELSE 0 END
            
            --UPDATE CURRENT BALLANCE
            ,BALANCE[ITERATION_NUMBER + 1,FOR GRADE_NO FROM 1 TO 5 INCREMENT 1] = BALANCE[CV(), CV()] - RES_COUNT[CV(),CV()]
            --UPDATE NEXT BALANCE
            ,BALANCE[ITERATION_NUMBER + 2,FOR GRADE_NO FROM 1 TO 5 INCREMENT 1] = BALANCE[ITERATION_NUMBER + 1, CV()]
            )
            )
            
            SELECT
              RES_ID
              ,GRADE
              ,RES_COUNT
            
            FROM
              MDL
            WHERE
              RES_COUNT > 0
              AND GRADE_NO > 0
            
            ORDER BY
              1,2
            ;
            
            RES_ID GRADE RES_COUNT
            ------ ----- ---------
            R1     A             2 
            R2     B             1 
            R3     E             2 
            R4     C             1 
            R4     D             1 
            R5     E             3 
            
             6 rows selected 
            The output format is a bit different from what you posted but the values are correct.
            It's up to you to beautify the output if necessary.

            I'm sure this can be optimized but I don't have time to do so now.

            Regards,

            Peter
            • 3. Re: Seat Distribution-Can we do this in SQL?
              jihuyao
              Agree, a clear and straight logic but difficult to implement in sql due to dynamic nature of the look-up seat balance.

              To extreme, recursive sql can process all records one by one but still lack flexibility compared to sql with model clause or pl/sql.

              Have not got through a scalable solution using recursive sql but for now come with a practical one (in short version, consider only grade A and B without changing the whole logic),
              with v_seat_reservation as (
              select sr.*, sm.grade gid,
              row_number() over (order by sr.res_id, sm.grade) pid,
              sr.res_count res_bal
              from seat_reservation sr, seat_master sm
              where (sr.group_flag='Y' and sr.grade=sm.grade)
              or (sr.group_flag='N' and sr.grade<=sm.grade)
              ),
              tmp (res_id, grade, res_count, group_flag, gid, pid, A, B, res_bal, res_amt) as (
              select null, null, null, null, null, 0, 3, 2, 0, 0 from dual
              union all
              select
              s.res_id, s.grade, s.res_count, s.group_flag, s.gid, s.pid,
              --t.A, t.B, t.res_bal, t.res_amt
              --
              case
               when decode(s.res_id, t.res_id, t.res_bal, s.res_count)>(case when s.group_flag='N' then
                       decode(s.grade, 'A', t.A, 0)+decode(s.grade, 'A', t.B, 'B', t.B, 0)
                        when s.group_flag='Y' then
                       decode(s.grade, 'A', t.A, 'B', t.B)
                          end) then t.A
               else case
                when s.gid='A' then
                 case
                   when t.A>=decode(s.res_id, t.res_id, t.res_bal, s.res_count) then t.A-decode(s.res_id, t.res_id, t.res_bal, s.res_count)
                   else 0
                 end
                     else t.A
               end
              end as A,
              --
              case
               when decode(s.res_id, t.res_id, t.res_bal, s.res_count)>(case when s.group_flag='N' then
                       decode(s.grade, 'A', t.A, 0)+decode(s.grade, 'A', t.B, 'B', t.B, 0)
                        when s.group_flag='Y' then
                       decode(s.grade, 'A', t.A, 'B', t.B)
                          end) then t.B
               else
                case
                 when s.gid='B' then
                  case
                   when t.B>=decode(s.res_id, t.res_id, t.res_bal, s.res_count) then t.B-decode(s.res_id, t.res_id, t.res_bal, s.res_count)
                   else 0
                  end
                 else t.B
                end
              end as B,
              --
              case
               when decode(s.res_id, t.res_id, t.res_bal, s.res_count)>(case when s.group_flag='N' then
                       decode(s.grade, 'A', t.A, 0)+decode(s.grade, 'A', t.B, 'B', t.B, 0)
                        when s.group_flag='Y' then
                       decode(s.grade, 'A', t.A, 'B', t.B)
                          end) then t.res_bal
               else case
                when s.gid='A' then  case
                    when t.A>=decode(s.res_id, t.res_id, t.res_bal, s.res_count) then 0
                    else decode(s.res_id, t.res_id, t.res_bal, s.res_count)-t.A
                   end
                     when s.gid='B' then  case
                    when t.B>=decode(s.res_id, t.res_id, t.res_bal, s.res_count) then 0
                    else decode(s.res_id, t.res_id, t.res_bal, s.res_count)-t.B
                   end
               end
              end as res_bal,
              --
              case
               when decode(s.res_id, t.res_id, t.res_bal, s.res_count)>(case when s.group_flag='N' then
                       decode(s.grade, 'A', t.A, 0)+decode(s.grade, 'A', t.B, 'B', t.B, 0)
                        when s.group_flag='Y' then
                       decode(s.grade, 'A', t.A, 'B', t.B)
                          end) then 0
               else case
                when s.gid='A' then  case
                    when t.A>=decode(s.res_id, t.res_id, t.res_bal, s.res_count) then decode(s.res_id, t.res_id, t.res_bal, s.res_count)
                    else t.A
                   end
                     when s.gid='B' then case
                    when t.B>=decode(s.res_id, t.res_id, t.res_bal, s.res_count) then decode(s.res_id, t.res_id, t.res_bal, s.res_count)
                    else t.B
                   end
               end
              end as res_amt
              --
              from v_seat_reservation s, tmp t where s.pid=t.pid+1
              )
              select * from tmp
              where (grade=gid and res_amt=0) or (res_amt>0)
              /
              
              RES_I G  RES_COUNT G G        PID          A          B    RES_BAL    RES_AMT
              ----- - ---------- - - ---------- ---------- ---------- ---------- ----------
              R1    A          2 Y A          1          1          2          0          2
              R2    B          1 Y B          2          1          1          0          1
              R3    A          2 Y A          3          1          1          0          0
              
              SQL> select * from seat_master ;
              
              G    BALANCE
              - ----------
              A          3
              B          2
              
              SQL> select * from seat_reservation ;
              
              RES_I G  RES_COUNT G
              ----- - ---------- -
              R1    A          2 Y
              R2    B          1 Y
              R3    A          2 Y
              • 4. Re: Seat Distribution-Can we do this in SQL?
                Peter vd Zwan
                Hi,

                As I said before the query can be optimized.

                I also solved a problem for the group_flag = 'N'
                It showed to many seats assigned in the case there are too many availlable.

                See the result below:
                WITH BAL AS
                --ADD A '0' SEAT TO THE LIST OF SEATS
                  (select * from seat_master UNION ALL
                  SELECT '0', 0 FROM DUAL
                  )
                ,MOD_IN AS
                  (
                  SELECT
                    SR.RES_ID
                    ,RANK() OVER (PARTITION BY BAL.GRADE ORDER BY SR.RES_ID) RES_NO
                    ,BAL.GRADE
                    ,BAL.BALANCE
                    ,SR.GRADE    RES_GRADE
                    ,CASE WHEN BAL.GRADE = '0' THEN SR.RES_COUNT ELSE 0 END RES_COUNT
                    ,SR.GROUP_FLAG
                  FROM
                    SEAT_RESERVATION SR
                    ,BAL
                  )
                ,MDL AS
                  (
                  SELECT
                    *
                  FROM
                    MOD_IN
                  MODEL
                  DIMENSION BY (RES_NO, GRADE)
                  MEASURES ( RES_ID, BALANCE,RES_GRADE, RES_COUNT, GROUP_FLAG GF)
                  RULES
                  SEQUENTIAL ORDER
                  ITERATE(10) UNTIL (RES_ID[ITERATION_NUMBER + 1,0] IS NULL)
                    (
                    --DISTRIBUTE SEATS PER REQUEST
                    RES_COUNT[ITERATION_NUMBER + 1, FOR GRADE in (select grade FROM BAL WHERE GRADE > '0' ORDER BY GRADE )] =
                            CASE WHEN GF[CV(),CV()] = 'Y' 
                                      AND MAX(RES_COUNT) [CV(), GRADE > '0'] = 0
                                      AND BALANCE[CV(), CV()] >= RES_COUNT[CV(),0]
                                      AND RES_GRADE[CV(), CV()] <= CV(GRADE)
                                 THEN RES_COUNT[CV(),0] 
                        
                                      WHEN GF[CV(),CV()] = 'N' 
                                      AND SUM(RES_COUNT) [CV(), GRADE > '0'] < RES_COUNT[ITERATION_NUMBER + 1,0]
                                      AND SUM(BALANCE) [CV(), GRADE >= RES_GRADE[CV(),CV()] ] >= RES_COUNT[CV(),0]
                                      AND RES_GRADE[CV(), CV()] <= CV(GRADE)
                                 THEN LEAST(2 * RES_COUNT[CV(),0] - SUM(RES_COUNT) [CV(), GRADE < CV(GRADE)], BALANCE[CV(), CV()] )
                                 ELSE 0 END
                     
                    --UPDATE CURRENT BALLANCE
                    ,BALANCE[ITERATION_NUMBER + 1, ANY] = BALANCE[CV(), CV()] - RES_COUNT[CV(),CV()]
                    --UPDATE NEXT BALANCE
                    ,BALANCE[ITERATION_NUMBER + 2, ANY] = BALANCE[ITERATION_NUMBER + 1, CV()]
                    )
                  )
                
                SELECT
                  MDL.RES_ID
                  ,MDL.GRADE || COUNT(1) OVER (PARTITION BY MDL.GRADE ORDER BY MDL.RES_ID, SC.LVL) SEAT
                FROM
                  MDL
                  ,(SELECT LEVEL LVL FROM DUAL CONNECT BY LEVEL < 100)  SC
                
                WHERE
                  MDL.RES_COUNT       > 0
                  AND MDL.GRADE       > '0'
                  AND SC.LVL          <= MDL.RES_COUNT
                
                ORDER BY
                  1,2
                ;
                
                RES_ID SEAT                                    
                ------ -----------------------------------------
                R1     A1                                        
                R1     A2                                        
                R2     B1                                        
                R3     E1                                        
                R3     E2                                        
                R4     C1                                        
                R4     D1                                        
                R5     E3                                        
                R5     E4                                        
                R5     E5                                        
                
                 10 rows selected 
                Regards,

                Peter

                Edited by: Peter vd Zwan on Nov 27, 2012 1:35 PM
                Modified the final output to match OP output
                • 5. Re: Seat Distribution-Can we do this in SQL?
                  Hoek
                  Thanks a lot, Peter!
                  Your query will most certainly keep me quiet for the coming weekend ;)
                  • 6. Re: Seat Distribution-Can we do this in SQL?
                    887479
                    Great...! Thats the only word I could say.... It will take days for me to understand the query.. And I am sure I will have to come back for "clarifications"..

                    And sorry for the delay, I was not keeping well..
                    • 7. Re: Seat Distribution-Can we do this in SQL?
                      887479
                      Thanks Hoek, for the bump in my absence...
                      • 8. Re: Seat Distribution-Can we do this in SQL?
                        Hoek
                        MODEL clause is documented, and explained in several Oracle related blogs/sites.
                        You could start exploring it here:
                        http://rwijk.blogspot.nl/2007/10/sql-model-clause-tutorial-part-one.html
                        http://www.sqlsnippets.com/en/topic-11701.html
                        • 9. Re: Seat Distribution-Can we do this in SQL?
                          Hoek
                          884476 wrote:
                          Thanks Hoek, for the bump in my absence...
                          No problem, I should be thanking you for your challenging question including a complete and working testcase ( oh, I did that already ;) ).
                          Will be looking myself for other ways to answer your question, besides the solutions posted so far, since it demonstrates the powers of SQL very well and your question applies to a lot of every-day, real-life requirements I ran into in the past and most certainly will do in the future.
                          • 10. Re: Seat Distribution-Can we do this in SQL?
                            Peter vd Zwan
                            Hi,

                            Thanks for the nice question. I enjoyed solving it.
                            I tried in other ways with recursive subquery factoring etc. but these methods are much more difficult then using the model clause.

                            To see how it works first look output of MDL after commenting all rules.
                            Then set the iteration to 1 and add the rules one by one.
                            Then increase the iteration number and see what happens.

                            Regards,

                            Peter
                            • 11. Re: Seat Distribution-Can we do this in SQL?
                              BrendanP
                              Here's a solution via recursive subquery factoring.
                              WITH res AS (
                              SELECT  res_id,
                                  Row_Number() OVER (ORDER BY res_id) rn,
                                  grade,
                                  res_count,
                                  group_flag
                                FROM seat_reservation
                              ), rsf (all_grade, all_amt, bal, res_id, rn, res_grade, res_count, group_flag) AS (
                              SELECT sms.grade           all_grade,
                                     CASE WHEN sms.grade < res.grade THEN 0
                                          WHEN res.group_flag = 'Y' THEN
                                            CASE WHEN Count (CASE WHEN sms.grade >= res.grade AND sms.balance >= res.res_count THEN 1 END)
                                                             OVER () = 0 THEN 0
                                                 ELSE
                                                   CASE WHEN Count (CASE WHEN sms.grade >= res.grade AND sms.balance >= res.res_count THEN 1 END)
                                                                    OVER (ORDER BY sms.grade RANGE UNBOUNDED PRECEDING) = 1 AND
                                                             sms.balance >= res.res_count THEN res.res_count
                                                        ELSE 0
                                                   END
                                            END
                                          ELSE
                                            CASE WHEN Sum (CASE WHEN sms.grade >= res.grade THEN sms.balance END)
                                                           OVER () < res.res_count THEN 0
                                                 ELSE
                                                   Greatest (0, Least (sms.balance, res.res_count + sms.balance -
                                                                Sum (CASE WHEN sms.grade >= res.grade THEN sms.balance END)
                                                                     OVER (ORDER BY sms.grade RANGE UNBOUNDED PRECEDING)))
                                            END
                                     END                  all_amt,
                                     sms.balance          bal,
                                     res.res_id,
                                     res.rn,
                                     res.grade            res_grade,
                                     res.res_count,
                                     res.group_flag
                                FROM seat_master          sms
                                JOIN res
                                  ON res.rn               = 1
                               UNION ALL
                              SELECT rsf.all_grade,
                                     CASE WHEN rsf.all_grade < res.grade THEN 0
                                          WHEN res.group_flag = 'Y' THEN
                                            CASE WHEN Count (CASE WHEN rsf.all_grade >= res.grade AND rsf.bal - rsf.all_amt >= res.res_count THEN 1 END)
                                                             OVER () = 0 THEN 0
                                                 ELSE
                                                   CASE WHEN Count (CASE WHEN rsf.all_grade >= res.grade AND rsf.bal - rsf.all_amt >= res.res_count THEN 1 END)
                                                                    OVER (ORDER BY rsf.all_grade RANGE UNBOUNDED PRECEDING) = 1 AND
                                                             rsf.bal - rsf.all_amt >= res.res_count THEN res.res_count
                                                        ELSE 0
                                                   END
                                            END
                                          ELSE
                                            CASE WHEN Sum (CASE WHEN rsf.all_grade >= res.grade THEN rsf.bal - rsf.all_amt END)
                                                           OVER () < res.res_count THEN 0
                                                 ELSE
                                                   Greatest (0, Least (rsf.bal, res.res_count + rsf.bal - rsf.all_amt -
                                                                Sum (CASE WHEN rsf.all_grade >= res.grade THEN rsf.bal - rsf.all_amt END)
                                                                     OVER (ORDER BY rsf.all_grade RANGE UNBOUNDED PRECEDING)))
                                            END
                                     END                     all_amt,
                                     rsf.bal - rsf.all_amt,
                                     res.res_id,
                                     res.rn,
                                     res.grade,
                                     res.res_count,
                                     res.group_flag
                                FROM rsf
                                JOIN res
                                  ON res.rn               = rsf.rn + 1
                              )
                              SELECT res_id id, res_grade "Req Grade", res_count "Req Qty", group_flag, all_grade "Alloc Grade", all_amt "Alloc Qty", bal - all_amt bal
                                FROM rsf
                               WHERE all_amt > 0
                               ORDER BY res_id, res_grade, all_grade
                              /
                              
                              ID    Req Grad Req Qty G Alloc Grade  Alloc Qty        BAL
                              ----- -------- ------- - ----------- ---------- ----------
                              R1    A              2 Y A                    2          1
                              R2    B              1 Y B                    1          1
                              R3    A              2 Y E                    2          4
                              R4    C              2 N C                    1          0
                              R4    C              2 N D                    1          0
                              R5    E              3 N E                    3          1
                              
                              6 rows selected.
                              
                              SQL> 
                              How it works

                              If we have the balances available for each grade after reservation R(i-1), then we can compute the allocations for R(i), and consequently the new balances, hence recursion. The recursion record structure contains the reservation fields, plus the allocated grade and amount and (prior) balance, with one record for each possible grade. At each iteration we join the current reservation to the previous set of balances. The allocation calculation uses CASE expressions and analytic functions across the grades dimension. At the end, we just exclude zero allocations. I have not bothered to split individual allocations into separate tickets.

                              Edited by: BrendanP on 01-Dec-2012 04:18

                              Actually, looking at Peter's addition of a dummy seat record, it occurred to me that I could avoid the duplication of the complex allocation logic in the anchor branch by the use of a dummy zero'th reservation, like this:
                              WITH res AS (
                              SELECT res_id,
                                     Row_Number() OVER (ORDER BY res_id) rn,
                                     grade,
                                     res_count,
                                     group_flag
                                FROM seat_reservation
                               UNION
                              SELECT 'R0',
                                     0,
                                     'X',
                                     0,
                                     'Y'
                                FROM DUAL
                              ), rsf (all_grade, all_amt, bal, res_id, rn, res_grade, res_count, group_flag) AS (
                              SELECT sms.grade            all_grade,
                                     0                    all_amt,
                                     sms.balance          bal,
                                     res.res_id,
                                     res.rn,
                                     res.grade            res_grade,
                                     res.res_count,
                                     res.group_flag
                                FROM seat_master          sms
                                JOIN res
                                  ON res.rn               = 0
                               UNION ALL
                              SELECT rsf.all_grade,
                                     CASE WHEN rsf.all_grade < res.grade THEN 0
                                          WHEN res.group_flag = 'Y' THEN
                                            CASE WHEN Count (CASE WHEN rsf.all_grade >= res.grade AND rsf.bal - rsf.all_amt >= res.res_count THEN 1 END)
                                                             OVER () = 0 THEN 0
                                                 ELSE
                                                   CASE WHEN Count (CASE WHEN rsf.all_grade >= res.grade AND rsf.bal - rsf.all_amt >= res.res_count THEN 1 END)
                                                                    OVER (ORDER BY rsf.all_grade RANGE UNBOUNDED PRECEDING) = 1 AND
                                                             rsf.bal - rsf.all_amt >= res.res_count THEN res.res_count
                                                        ELSE 0
                                                   END
                                            END
                                          ELSE
                                            CASE WHEN Sum (CASE WHEN rsf.all_grade >= res.grade THEN rsf.bal - rsf.all_amt END)
                                                           OVER () < res.res_count THEN 0
                                                 ELSE
                                                   Greatest (0, Least (rsf.bal, res.res_count + rsf.bal - rsf.all_amt -
                                                                Sum (CASE WHEN rsf.all_grade >= res.grade THEN rsf.bal - rsf.all_amt END)
                                                                     OVER (ORDER BY rsf.all_grade RANGE UNBOUNDED PRECEDING)))
                                            END
                                     END                     all_amt,
                                     rsf.bal - rsf.all_amt,
                                     res.res_id,
                                     res.rn,
                                     res.grade,
                                     res.res_count,
                                     res.group_flag
                                FROM rsf
                                JOIN res
                                  ON res.rn               = rsf.rn + 1
                              )
                              SELECT res_id id, res_grade "Req Grade", res_count "Req Qty", group_flag, all_grade "Alloc Grade", all_amt "Alloc Qty", bal - all_amt bal
                                FROM rsf
                               WHERE all_amt > 0
                               ORDER BY res_id, res_grade, all_grade
                              • 12. Re: Seat Distribution-Can we do this in SQL?
                                Peter vd Zwan
                                Hi,

                                Brendan, I tried your queries But I receive the following error:
                                ORA-32486: unsupported operation in recursive branch of recursive WITH clause

                                My version info is:
                                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production     
                                PL/SQL Release 11.2.0.1.0 - Production                                           
                                CORE     11.2.0.1.0     Production                                                         
                                TNS for 64-bit Windows: Version 11.2.0.1.0 - Production                          
                                NLSRTL Version 11.2.0.1.0 - Production                                           
                                What version of oracle are you using? I also tried with recursive subquery factoring but I can not use analitical nor argegated functions in this version.

                                Regards,

                                Peter
                                • 13. Re: Seat Distribution-Can we do this in SQL?
                                  BrendanP
                                  Peter, I am using the 32-bit XE version 11.2 but I don't have access to it right now so not sure of the sub-versions.

                                  Are you sure, I though analytics were allowed from the start with RSF (maybe not aggregation)?

                                  Edited by: BrendanP on 03-Dec-2012 07:26

                                  Edited by: BrendanP on 03-Dec-2012 07:36
                                  Actually, as luck would have it, I have access to a database of the same version as you, and yes, I get the same error - it seems a later version is needed for my query.

                                  Edited by: BrendanP on 03-Dec-2012 07:44

                                  But it should work, because the the 11.2 manual says:

                                  The recursive member cannot contain any of the following elements:
                                  +■ The DISTINCT keyword or a GROUP BY clause+
                                  +■ The model_clause+
                                  +■ An aggregate function. However, analytic functions are permitted in the select list.+
                                  +■ Subqueries that refer to query_name.+
                                  +■ Outer joins that refer to query_name as the right table.+
                                  • 14. Re: Seat Distribution-Can we do this in SQL?
                                    Peter vd Zwan
                                    Hi,

                                    Thanks for checking.

                                    I always thought this was "as designed" behavier.

                                    I will raise a seperate question about that here later.

                                    Regards,

                                    Peter