SQL & PL/SQL

Hi,

I am trying to solve a problem using analytical functions but I am stuck.

1. I have a list of coupons that I can use. The usage sequence is in alphabetical order of the coupon name

   1. 

2. There is a limit (cap) on the total value across all coupons that can be used in a day

   1. 

3. Each coupon is subject to 1 or 2 caps. If it is subject to 2 caps, there is a specified sequence to apply caps.

   1. 

4. I have to now find how much coupon value could be utilized before my daily cap was reached. i.e. find "coupon usage" and “Cap Remaining” below.

   So, If I join the tables above

   1. 

      I am trying to compute the last 2 column values.

   Explanation:

   Row #1 : Coupon A has a value of 100 and that is less than the first cap i.e. Cap 1 with a limit of 150. So we use all of Coupon A. Cap 1 has a limit of 50 remaning.

   Row #2: Coupon A was all used against Cap 1 and we did not have to consume Cap 2.

   Row #3: Coupon B of value 40 and it is less than Cap 2 with a limit of 70. So we use all of Coupon B. Cap 2 has now 30 remaining.

   Row #4: Coupon C has a value of 120. It is subject to Cap 2 first that has 30 remaining. So we use 30 and Cap 2 now has 0 remaining. Coupon C has (120 - 30) i.e. 90 residual value remaining.

   Row #5: Coupon C has a 90 value remaning that is now capped against Cap 1 with a 50 limit. So we use 50 and Cap 1 becomes 0

   Row #6 - #8: Caps have become 0. Nothing can be used.

I am able to make this work using analytical function with a single cap. The 2 caps with a possibly different usage sequence has me stuck.

with coupon_data(coupon, value)as 
(
select 'A',100 from dual union all
select 'B',40 from dual union all
select 'C',120 from dual union all
select 'D',10 from dual union all
select 'E',200 from dual 
)
, cap_data(cap_name, cap_limit) as
(
select 'Cap1', 150 from dual union all
select 'Cap2', 70 from dual 
)
, coupon_cap_mapping(coupon, cap_sequence, cap_name) as
(
select 'A',1,'Cap1' from dual union all
select 'A',2,'Cap2' from dual union all
select 'B',1,'Cap2' from dual union all
select 'C',1,'Cap2' from dual union all
select 'C',2,'Cap1' from dual union all
select 'D',1,'Cap1' from dual union all
select 'E',1,'Cap1' from dual union all
select 'E',2,'Cap2' from dual 
)
SELECT cd.coupon,
       cd.value,
       cap_d.cap_name,
       ccm.cap_sequence,
       cap_d.cap_limit
       --, coupon_usage, cap_remaining 
FROM coupon_data cd
JOIN coupon_cap_mapping ccm ON ( cd.coupon = ccm.coupon )
JOIN cap_data           cap_d ON ( cap_d.cap_name = ccm.cap_name )
ORDER BY cd.coupon,
         ccm.cap_sequence;