Hi,
I am trying to solve a problem using analytical functions but I am stuck.
-
I have a list of coupons that I can use. The usage sequence is in alphabetical order of the coupon name.
-
Coupons
Value
A
100
B
40
C
120
D
10
E
200
-
There is a limit (cap) on the total value across all coupons that can be used in a day
-
Cap Name
Cap Limit
Cap 1
150
Cap 2
70
-
Each coupon is subject to 1 or 2 caps. If it is subject to 2 caps, there is a specified sequence to apply caps.
-
Coupon
Cap Sequence
Cap Name
A
1
Cap 1
A
2
Cap 2
B
1
Cap 2
C
1
Cap 2
C
2
Cap 1
D
1
Cap 1
E
1
Cap 1
E
2
Cap 2
-
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
-
Coupon
Value
Cap Name
Cap Sequence
Cap Limit
Coupon Usage
Cap Remaining
1
A
100
Cap 1
1
150
100
50
2
A
100
Cap 2
2
70
0
70
3
B
40
Cap 2
1
70
40
30
4
C
120
Cap 2
1
70
30
0
5
C
120
Cap 1
2
150
50
0
6
D
10
Cap 1
1
150
0
0
7
E
200
Cap 1
1
150
0
0
8
E
200
Cap 2
2
70
0
0
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. No coupon 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;