Summing multiple columns per row, with dynamic amount to calculate

There is a table for calculating extra-time working hours:

(Fiddle: https://dbfiddle.uk/Plsd2pdX)

The table represents the extra hours for each worker, and which type of extra hour is it (1.5x, 2x or 3x of base salary), and the actual amount to pay for these hours.

I want to select the total approved sum of each row based on the approval type - if it's approved, then sum all the sums (1.5x, 2x and 3x sums). If it's not approved, it will be 0.

If it's partially approved, sum the partial approved sum, starting from the higher amount. In the above example, the 3rd row is partially approved with 5 hours, so it should start summing from the 3x to below. so take all the 3 hours from the 3x, and then 2 hours from the 2x, and 1 remaining hour from the 1.5x, which will then be summed up to:

180 + 100 + (50/2*1) = 305

so the result would be:

I thought about using CASE, but the problem here is that if the approved type is partial, then I also need to count the remaining extra hours as I go from the higher to the lower type and I was not able to do it inside the CASE

Here's a potential solution:

select worker_id
, sum(case when is_approved = 'yes' then s when is_approved = 'no' then 0
   else case when aggtime <= approved_amount then s else s / (h * 1.0 * (aggtime - approved_amount))  end  end)
  , approved_amount
  from (
SELECT e.id, e.worker_id, is_approved, approved_amount, h.*
   , sum(h.h) over(partition by worker_id order by sort desc) as aggTime
  FROM extraTime e
cross apply (
    select extra_hours_1_5x_amount as h, extra_hours_1_5x_sum as s, 1 as sort
     union all
    select extra_hours_2x_amount,extra_hours_2x_sum, 2
   union all
  select extra_hours_3x_amount, extra_hours_3x_sum, 3
    ) h
  ) x
group by worker_id,approved_amount
order by worker_id

I do a little unpivot to get the hours separated and aggregate them ordered by approval hours starting from the highest.
Then to get the total sum, i check which of the approval condition is applied and do the formula you mentioned

Here is my idea of how to solve it
There are 3 case statements and the "when part" part is the one I need to explain.

select worker_id, 
       case is_approved
         when 'yes' then isnull(extra_hours_1_5x_sum, 0) + isnull(extra_hours_2x_sum, 0) + isnull(extra_hours_3x_sum, 0)
         when 'part' then /* calculate 3x hours approved */          least(isnull(approved_amount, 0), isnull(extra_hours_3x_amount, 0)) * (isnull(extra_hours_3x_sum, 1) / isnull(extra_hours_3x_amount, 1)) +
                          /* calculate 2x hours approved if left */  least(isnull(approved_amount, 0) - isnull(extra_hours_3x_amount, 0), isnull(extra_hours_2x_amount, 0)) * (isnull(extra_hours_2x_sum, 1) / isnull(extra_hours_2x_sum, 1)) + 
                          /* calculate 1.5x hours approved if left */least(isnull(approved_amount, 0) - isnull(extra_hours_3x_amount, 0) - isnull(extra_hours_2x_amount, 0), isnull(extra_hours_1_5x_amount, 0)) * (isnull(extra_hours_1_5x_sum, 0) / isnull(extra_hours_1_5x_amount, 0))
         when 'no' then 0
       end total_sum
  from extraTime

The "when 'part'" explanation:
first line calculates 3x_hour pro hour price

isnull(extra_hours_3x_sum, 1) / isnull(extra_hours_3x_amount, 1)

and multiplies it with either approved_amount or extra_hours_3x_amount.Depending on which one is smaller.

second and third line do the following: it takes amount of time left from previous steps

least(isnull(approved_amount, 0) - isnull(extra_hours_3x_amount, 0), isnull(extra_hours_2x_amount, 0))

and multiplies it with respective hour price for 2x of 1_5x hours.

It's not a universal solution but it should be working for your specific case

Check the dbfiddle