I have to compare total _revenue with target and multiplied with respective percent column. So once 750 is greater than 700 then it should multiply with 0.02 and ignore 500 and 400. and if total_revenue is 470 then multiplied by 1% and ignores other.
sample data is like this
branch_id total_revenue target percent
2356 750 500 1.5%
2356 750 700 2%
2356 750 450 1%
2458 750 500 1.5%
2458 750 700 2%
2458 750 450 1%
desired output
branch_id total_revenue target percent test percent
2356 750 700 2% 15
2458 750 700 2% 15
I am trying
with revenue_cal(select a+b as total_revenue from dummy table)
,percent_cal as (
select a, b, .....
, (case when (select total_revenue from revenue_cal) > (target)
then((percent/100)*total_revenue)
else 0 end) as test_percent)
I can't use max function here because I have eighty columns and I don't want to group by on them, I can just group by on id. It work fine if total revenue is less than smallest number but then other values repeat. I don't want repetition.
branch_id total_revenue target percent target percent
2356 450 500 1.5% 00
2356 450 700 2% 00
2356 450 450 1% 4.5
2458 450 500 1.5% 00
2458 450 700 2% 00
2458 450 450 1% 4.5
it should be
branch_id total_revenue target percent target percent
2356 450 450 1% 4.5
2458 450 450 1% 4.5
How can I get desired output?
I suppose that, for each branch, you want the row whose total is equal to the target, or with the "closest" greater total.
If so, you can use row_number like so:
select
from (
select t.*,
row_number() over(partition by branch_id order by total_revenue - target) rn
where total_revenue >= target
from mytable t
) t
where rn = 1