CODEWITHSUNDEEP
sqloracle-database
Mansoor Ali
Mansoor Ali

Reputation: 21

Oracle SQL Query to Distribute a value into different rows based on priority

I want to write SQL query which distribute or adds a value into different rows based on priority.

Here is my scenario.

I have table called M_FLIGHT with below data.

DEPARTURE_DATE  FLIGHT_NO  FAIR_TYPE          PRIORITY  AVAILABLE_SEATS  MAX_CAPACITY  RETURN_SEAT
==============  =========  =================  ========  ===============  ============  ===========
05-DEC-14       SC-917     Normal Fair           1         7                10             4
05-DEC-14       SC-917     Maharaja Standard     2         8                10             0
05-DEC-14       SC-917     Maharaja Special      3         9                10             0

A flight can have different fair types( i.e Normal Fair, Maharaja Standard, Maharaja Delhi Special so on) with priorities 1, 2 and 3 respectively.

If user cancels/returns the booked seats then I need to add RETURN_SEAT value to the AVAILABLE_SEATS value such that it should not exceed the MAX_CAPACITY value for that fair type.

If it exceeds then add remaining value to next priority(i.e to next fair type).

So, the final result should be as below

DEPARTURE_DATE  FLIGHT_NO  FAIR_TYPE          PRIORITY  AVAILABLE_SEATS  MAX_CAPACITY  RETURN_SEAT
==============  =========  =================  ========  ===============  ============  ===========
05-DEC-14       SC-917     Normal Fair           1         10               10             4
05-DEC-14       SC-917     Maharaja Standard     2         9                10             0
05-DEC-14       SC-917     Maharaja Special      3         9                10             0

Upvotes: 2

Views: 1350

Answers (3)

atufi
atufi

Reputation: 11

Based on Gordon Linoff answer

I think this query could help you

select f.*,
       (case
         when 4 >= free_seat_sumover - free_seat then
          least(4 - (free_seat_sumover - free_seat), free_seat)
       
         else
          0
       end) as new_occupied
  from (select f.*,
               (max_capacity - available_seats) as free_seat,
               sum(max_capacity - available_seats) over(order by priority) as free_seat_sumover
          from m_flight f) f;

which 4 is the RETURN_SEAT column value

Upvotes: 0

Ramin Mirahmadi
Ramin Mirahmadi

Reputation: 141

I wrote a update query for this business.

update M_FLIGHT d
   set d.AVAILABLE_SEATS =
       (select case
                 when (f.available_seats + Case
                        when ((select sum(s.return_seat) from M_FLIGHT s) -
                             nvl((select sum(s.max_capacity - s.available_seats)
                                    from M_FLIGHT s
                                   where s.priority < f.priority),
                                  0)) > 0 THEN
                         ((select sum(s.return_seat) from M_FLIGHT s) -
                         nvl((select sum(s.max_capacity - s.available_seats)
                                from M_FLIGHT s
                               where s.priority < f.priority),
                              0))
                        Else
                         0
                      END) >= f.max_capacity then
                  f.max_capacity
                 else
                  (f.available_seats + Case
                    when ((select sum(s.return_seat) from M_FLIGHT s) -
                         nvl((select sum(s.max_capacity - s.available_seats)
                                from M_FLIGHT s
                               where s.priority < f.priority),
                              0)) > 0 THEN
                     ((select sum(s.return_seat) from M_FLIGHT s) -
                     nvl((select sum(s.max_capacity - s.available_seats)
                            from M_FLIGHT s
                           where s.priority < f.priority),
                          0))
                    Else
                     0
                  END)
               end as avail_new
          from M_FLIGHT f
         where f.priority = d.priority);

I know that's to hard to read this query, but it works. Maybe you need to add some filters for sub queries (like DEPARTURE_DATE,FLIGHT_NO,...).

Thank you for Bring up some issue that needs to think a lot. I liked it.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can calculate the occupied space using a cumulative sum:

select f.*, sum(max_capacity-available_seats) as occupied,
        sum(max_capacity-available_seats) over (order by priority) as cumeocc
from m_flight f;

With this information, you can allocate v_Num new seats:

select f.*,
       (case when v_Num >= cumeocc - occupied
             then greatest(v_num - (cumeocc - occupied), occupied)
             else occupied
        end) as new_occupied
from (select f.*, (max_capacity-available_seats) as occupied,
              sum(max_capacity-available_seats) over (order by priority) as cumeocc
      from m_flight f
     ) f;

Then the next step is to merge this information back. That is a bit hard to express, because you don't have an obvious key on each row. I would suggest you add a single-column primary key to the table.

Upvotes: 3

Related Questions