How to select aggregate discount percentages and flat amount?-CodePudding

I have a scenario whereby I need to aggregate n number of discounts to get a total discount. Each discount must be applied net of the previous discount.

For example: I have an order of 200 Rs. (Sum of amount) and I have multiple vouchers. The first gets me 15% off, 200-(200*(15/100)) = 170. And then we have a second voucher worth Flat 10 Rs., 170-(10) = 160.

Sequence is important, so a further field records the order in which the discounts are applied.

Below are table:

order

id  order_id  productId  amount
 1    1      5          160
 2    1      9          40

So total amount without discount is: 200 Rs.

discount

id   order_id    seq   type          amt  
 1       1        1    Per (%)     15
 2       1        3    Flat        10

So, discount amount will be: ((200*(15/100))) 30 10 = 40 .

So I have tried to write SQL query with CTE but it is not giving expected output:

    WITH recursive cte_calctotalamount AS
(
         SELECT   order_id,
                  sum(amount) AS totalamount
         FROM     ORDER
         WHERE    order_id=1
         GROUP BY order_id ), 
cte_totaldiscountamount AS
(
           SELECT     i.order_id,
                      i.seq,
                      i.amt,
                      ta.totalamount AS totalamount,
                      CASE
                          WHEN i.type='Flat' THEN i.amt
                          WHEN i.type='Per' THEN (ta.totalamount * (i.amt/100))
                      END totaldiscountedamount,
                      (totalamount- (
                      CASE
                          WHEN i.type='Flat' THEN i.amt
                          WHEN i.type='Per' THEN (ta.totalamount * (i.amt/100))
                      END) ) amountafterdiscount
           FROM       discount i
           INNER JOIN cte_calctotalamount ta
           ON         ta.order_id=i.order_id

           UNION

           SELECT     d.order_id,
                      d.seq,
                      d.amt,
                      ad.totalamount,
                      CASE
                          WHEN d.type='Flat' THEN d.amt
                          WHEN d.type='Per' THEN (ad.amountafterdiscount - (d.amt/100))
                      END totaldiscountedamount,
                      (amountafterdiscount - (
                      CASE
                          WHEN d.type='Flat' THEN d.amt
                          WHEN d.type='Per' THEN (ad.amountafterdiscount - (d.amt/100))
                      END) ) amountafterdiscount
           FROM       discount d
           INNER JOIN cte_totaldiscountamount ad
           ON         d.order_id=ad.order_id
           AND        d.seq=ad.seq 1 )
SELECT *
FROM   cte_totaldiscountamount;

Please help to achieve below output,

order_id   totalAmount totalDiscountedAmount amountAfterDiscount 
   1          200            40                  160

CodePudding user response：

There are 4 things that you need to modify in your query

In recursive queries, you need to initialize the first result set. It is the basis of calculation of the next iterations. In this case, you will need to add in the first query in the recursive part where i.seq = 1 (we start with the initial discount).
Second, you are not adding the discounted amounts recursively. For that, you need to retrieve the row discount amount from previous iterations. so instead of :

    case
           when d.type='Flat' THEN d.amt
           WHEN d.type='Per' THEN (ad.AmountAfterDiscount - (d.amt/100))
         END totalDiscountedAmount

you should be writing:

totalDiscountedAmount   case 
       when d.type='Flat' THEN d.amt
       WHEN d.type='Per' THEN (ad.AmountAfterDiscount - (d.amt/100))
     END totalDiscountedAmount

You will need to add a new row incrementor in discounts. Recursive queries will end when the returned result set of the second query after Union is null. Since the the condition d.seq=ad.seq 1 will be false, the query will return nothing. It is due to the fact that in discounts table, your next sequence is 3 and not 2. In the proposed solution, you can see that it is returned in the CTE of discounts using ROW_NUMBER()
Finally, you'll need to keep only the last row (since the recursive query will return naturally N rows if N is the number of discounts for a certain order. You can simply do that by joining the last output with a subquery as shown in the example.

Your final query would look like:

        WITH RECURSIVE CTE_CalcTotalAmount
AS
(
   select order_id,sum(amount) As totalAmount from "order" 
    where order_id=1
    group by order_id
),
CTE_DiscountsPerOrder as (
    select order_id, seq, amt, type, row_number() over (partition by order_id order by seq asc ) as new_seq from discount  ) ,
CTE_TotalDiscountAmount AS
(
  select i.order_id,i.new_seq,i.amt,ta.totalAmount as TotalAmount,
    case 
       when i.type='Flat' THEN i.amt
       WHEN i.type='Per' THEN (ta.totalAmount * (i.amt/100))
     END totalDiscountedAmount,
     (totalAmount-
       (case 
           when i.type='Flat' THEN i.amt
           WHEN i.type='Per' THEN (ta.totalAmount * (i.amt/100))
       END)
     ) AmountAfterDiscount
    from CTE_DiscountsPerOrder i
    inner JOIN CTE_CalcTotalAmount ta ON ta.order_id=i.order_id
    where i.new_seq=1
  UNION
    select d.order_id,d.new_seq,d.amt,ad.totalAmount,
     totalDiscountedAmount  case 
       when d.type='Flat' THEN d.amt
       WHEN d.type='Per' THEN (ad.AmountAfterDiscount - (d.amt/100))
     END totalDiscountedAmount,
     (AmountAfterDiscount -
        (case 
       when d.type='Flat' THEN d.amt
       WHEN d.type='Per' THEN (ad.AmountAfterDiscount - (d.amt/100))
     END)
     ) amountAfterDiscount
    From CTE_DiscountsPerOrder d
    inner JOIN CTE_TotalDiscountAmount ad on d.order_id=ad.order_id
    AND d.new_seq=ad.new_seq 1
)
select * from CTE_TotalDiscountAmount a
join (select order_id, count(*) as totalDiscounts from CTE_DiscountsPerOrder group by 1) b on b.order_id = a.order_id  and b.totalDiscounts = a.new_seq;