How can I add condition for total sales per month for specific category

Question

Let's assume there is a table as below structure

select Year, Month , customer, Category, Amount 
from claim

I want to apply a discount for each month as shown in the table (green columns):

• if the 'Cus X' total sales for brand and generic categories in JAN is greater than 15000, then apply a discount for only 'Brand' category of 2%
• if it is greater than 20000 then apply a discount of 3%

and the same thing for other months and customers.

Is it possible to do that in SQL with a subquery or some functions that can help?

Please advise

Answer 1

You can use case .. when statement and analytical function as follows:

select Year, Month , customer, Category, Amount,
       Case when category = 'Brand'
            then
              Case when total_sales > 15000 then '2%'
                   When total_sales > 20000 then '3%'
              End
       End as disc,
       Case when category = 'Brand' 
            then
              Case when total_sales > 15000 then 2*amount/100
                   When total_sales > 20000 then 3*amount/100
              End
       End disc_amount
From
(select Year, Month , customer, Category, Amount,
        sum(case when category in ('Brand', 'Generic') then amount else 0 end)
          over (partition by year, month, customer) as total_sales
 from claim)