Forum Stats

  • 3,770,131 Users
  • 2,253,073 Discussions
  • 7,875,337 Comments

Discussions

Help with aggregated discounts

User_UBC84
User_UBC84 Member Posts: 3 Red Ribbon
edited Oct 19, 2021 3:05PM in SQL & PL/SQL

Hi all,

need help with following query to get an aggregated discount.

I have a item and some discounts to be applied depending on the bought quantity.

Stretch / FromUnit / DiscType / Discount

1 / 0 / Default / 5

1 / 0 / Extra / 15

2 / 7 / Extra / 17

3 / 10 / Extra / 20

1 / 0 / Bonus / 5


I need a query to get the following result:

Stretch / FromUnit / Discount

1 / 0 / 25

2 / 7 / 27

3 / 10 / 30


As you can see the default discount (5) applies to all quantities, same for bonus discount, and I need to combine it with the other discounts depending on the stretch they are asigned.

Find some approach using 'sum over partition' like the following but I'm not able to find the correct one...

select stretch, fromUnit, sum(discount) over (partition by stretch) discount

 from (select t.*

       ,row_number() over (partition by stretch, fromUnit, DiscType

                 order by rownum) as rn

     from table t);


Hope I explained myself and thanks in advance.

Alex

Tagged:

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 690 Silver Trophy

    sum(Discount)+sum(case when DiscType in ('Default','Bonus') then sum(Discount) end) over()

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    Hi, @User_UBC84


    Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data, so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

    It's not clear how you get those results. Show, step by step, how you calculate the discounts in the output.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    edited Oct 19, 2021 9:04PM

    I don't think this will not work.

    with sample(stretch,fromunit,disctype,discount)
      as (
          select 1,0,'Default',5 from dual union all
          select 1,0,'Extra',15 from dual union all
          select 2,7,'Extra',17 from dual union all
          select 3,10,'Extra',20 from dual union all
          select 1,0,'Bonus',5 from dual
         ),
    t as (
          select  stretch,
                  fromunit,
                  row_number() over(partition by stretch,fromunit order by 1) rn,
                  sum(case when disctype in ('Default','Bonus') then 0 else discount end) over(partition by stretch,fromunit) +
                    sum(case when disctype in ('Default','Bonus') then discount end) over() discount
            from  sample
          )
    select  stretch,
            fromunit,
            discount
      from  t
      where rn = 1
    /
    
    
       STRETCH   FROMUNIT   DISCOUNT
    ---------- ---------- ----------
             1          0         25
             2          7         27
             3         10         30
    
    SQL>
    
    

    SY.

  • User_UBC84
    User_UBC84 Member Posts: 3 Red Ribbon

    Hi Solomon,

    tried your option and works well but what if there is another discount which have more than one stretch? The sample would be the following:

    with sample(stretch,fromunit,disctype,discount)
      as (
          select 1,0,'Default',5 from dual union all
          select 1,0,'Extra',15 from dual union all
          select 2,7,'Extra',17 from dual union all
          select 3,10,'Extra',20 from dual union all
          select 1,0,'Bonus',5 from dual
          select 2,7,'Bonus',7 from dual
         ),
    

    As you can see, the bonus discount now has a second stretch and this should apply to the third stretch of 'Extra' so the result should be the following:

       STRETCH   FROMUNIT   DISCOUNT
    ---------- ---------- ----------
             1          0         25
             2          7         29
             3         10         32
    

    Tried to adapt your select to calculate two of them first and then another select over it to apply the other discount but cant make it work. I'm in the right way? I mean the same approach should work?


    Many thanks,

    Alex

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    Hi,

    You still haven't explained how you get the desired results. For example, for stretch=3 an d fromunit=10, why is discount=32? Why not 30, as in the first example. or 20? Show exactly how you arrive at the number 32.

  • User_UBC84
    User_UBC84 Member Posts: 3 Red Ribbon

    Hi,

    to get the discount what I do is the following:

    Stretch 1: 5 (default, stretch 1) + 15 (extra, stretch 1) + 5 (bonus, stretch 1) = 25

    Stretch 2: 5 (default, stretch 1, because it applies to all units from 0 to infinite) + 17 (extra, stretch 2) + 7 (bonus, stretch 2) = 29

    Stretch 3: 5 (default, stretch 1, because it applies to all units from 0 to infinite) + 20 (extra, stretch 3) + 7 (bonus, stretch 2, because it applies to all stretches from stretch 2) = 32

    Thanks

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Oct 20, 2021 12:16PM

    Hi, @User_UBC84

    with sample(stretch,fromunit,disctype,discount)
      as (
          select 1,0,'Default',5 from dual union all
          select 1,0,'Extra',15 from dual union all
          select 2,7,'Extra',17 from dual union all
          select 3,10,'Extra',20 from dual union all
          select 1,0,'Bonus',5 from dual
          select 2,7,'Bonus',7 from dual
         ),
    

    Would you like to get answers that work? Make sure the statements you post for the sample data work, too. Test (and, if necessary, fix) your statements before you post them.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    Hi, @User_UBC84

    So, every distinct value of stretch should have at least one 'Bonus', one 'Default' and one 'Extra' discount. If strecth x does not have one of those disctypes. you need to get the discount from a the last row (in order by strecth) that does have that disctype, provided that fromunit on that row is either 0 or is greater than x. For example, in the sample data you tried to post about two hours ago, strectch 3 does not have a 'Bonus' row, so you need to get the discount from some other 'Bonus' row. You could get it from the row where strectch=1 and fromunit=0, since 3 is between 1 and infinity (which is what fromunit=0 means) or you could get it from the row where strectch=2, since 3 is between 2 and 7. In this case, you get it from the row with the higher stretch. Do I understand how that works?

    If so, here's one way to do it:

    WITH  AllDiscTypes  AS
    (
    	SELECT DISTINCT  DiscType
    	FROM  		 sample
    )
    SELECT   s.Stretch, s.FromUnit
    ,	 SUM ( COALESCE ( s.Discount
    	   		 , (
    	   		    SELECT MAX (Discount) KEEP (DENSE_RANK LAST ORDER BY r.Stretch)
    	   		    FROM  sample r
    	   		    WHERE  a.DiscType = r.DiscType
    	 		  )
    	 		 )
    	   ) AS TotalDiscount
    FROM	  AllDiscTypes a
    LEFT JOIN sample    s PARTITION BY (s.Stretch, s.FromUnit)
       	 	      ON s.DiscType = a.DiscType
    GROUP BY s.Stretch, s.FromUnit
    ORDER BY s.Stretch
    ;
    


  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    edited Oct 20, 2021 1:13PM

    You need to explain logic. It appears you want to use last DEFAULT/BONUS discount ordered by STRECH, FROMUNIT. If so:

    with sample(stretch,fromunit,disctype,discount)
      as (
          select 1,0,'Default',5 from dual union all
          select 1,0,'Extra',15 from dual union all
          select 2,7,'Extra',17 from dual union all
          select 3,10,'Extra',20 from dual union all
          select 1,0,'Bonus',5 from dual union all
          select 2,7,'Bonus',7 from dual
         ),
    t as (
          select  stretch,
                  fromunit,
                  row_number() over(partition by stretch,fromunit order by 1) rn,
                  sum(case when disctype in ('Default','Bonus') then 0 else discount end) over(partition by stretch,fromunit) +
                    nvl(
                        last_value(case when disctype = 'Default' then discount end ignore nulls) over(order by stretch,fromunit),
                        0
                       ) +
                    nvl(
                        last_value(case when disctype = 'Bonus' then discount end ignore nulls) over(order by stretch,fromunit),
                        0
                       ) discount
            from  sample
          )
    select  stretch,
            fromunit,
            discount
      from  t
      where rn = 1
    /
    
       STRETCH   FROMUNIT   DISCOUNT
    ---------- ---------- ----------
             1          0         25
             2          7         29
             3         10         32
    
    SQL>
    
    

    SY.