I have a table as below. Here I need to check if the units fall under this threshold and check the discount type to apply the discount by percentage or by value. How can I achieve this?
Customer
Product
Units
Net Value
Threshold1
Threshold2
Threshold3
Threshold4
Threshold5
Discount Type
Discount Amt1
Discount Amt2
Discount Amt3
Discount Amt4
Discount Amt5
Discount %1
Discount %2
Discount %3
Discount %4
Discount %5
Cust1
P1
14
1000
5
10
15
20
Amount
25
50
75
100
125
Cust1
P2
20
2000
10
20
30
40
50
Percentage
5%
10%
15%
20%
25%
Cust2
P3
120
3000
25
50
100
Amount
50
100
150
Cust3
P4
30
4000
10
20
30
40
Percentage
10%
20%
30%
40%
Cust4
P5
40
5000
20
40
60
80
Amount
100
200
300
400
500
conditions should be as below.
Check Discount type (Amount or Percentage)
Then units should be verified with threshold –
T1 =< Units < T2 then discount Amt or % 1
T2 =< Units < T3 then discount Amt or % 2
T3 =< Units < T4 then discount Amt or % 3
T3 =< Units < T5 then discount Amt or % 4
T4 =< Units then discount Amt or % 5
We need to handle the Threshold null as well (ex: Cust2 & P3 where Units is 120 but max threshold is T3 (100) & T4 is null
