How to sum the value of another sum from same select statement-CodePudding

I am trying sum the value of another sum in the same select statement and then I want to check the sum value in case statement. When I do it, it is working instead it is just gets individual value.

I have to sum Billable_Trades and then I have to give some rate if the billable_trades is above some numbers for that, I need to know the total of the billable_trade.

select t.Business_Unit_Description, -- case when Product_Type_Description = 'Fee Based' then 'Fee Based' else '' end as revenue_type,
              billable_trades,
              isnull(c.comm_adjustments, 0) as commission_adjustments,
              rate,
              billable_trades*rate as charges,
              0.3 as commission_rate,
              isnull(c.comm_adjustments, 0)*0.3 as credit,
              (billable_trades*rate)- isnull(c.comm_adjustments, 0)*0.3 as total
            from   
       (
       select Business_Unit_Description,
        sum(billable_trades) as billable_trades, 
        CASE WHEN SUM(billable_trades) > 0 and SUM(billable_trades) <= 150000 THEN 0.85667 ELSE 0.47104 END as rate
      from   cte_combined
       group by Business_Unit_Description
       ) t
       left outer join cte_comm_adj c on c.Business_Unit_Description = t.Business_Unit_Description
order by t.Business_Unit_Description

CodePudding user response：

There is obviously more to the query than is shown - as you are using a derived table to reference a CTE and also outer joining to another CTE.

I would move the calculation of rate out of the derived table:

Select t.Business_Unit_Description -- case when Product_Type_Description = 'Fee Based' then 'Fee Based' else '' end as revenue_type,
     , t.sum_billable_trades
     , commission_adjustments = isnull(c.comm_adjustments, 0)
     , r.rate
     , charges                = t.sum_billable_trades * r.rate
     , commission_rate        = 0.3
     , credit                 = isnull(c.comm_adjustments, 0) * 0.3
     , total                  = (t.sum_billable_trades * r.rate) - isnull(c.comm_adjustments, 0) * 0.3
  From (Select Business_Unit_Description
             , sum_billable_trades = sum(billable_trades)
          From cte_combined
         Group By Business_Unit_Description) t
  Cross Apply (Values (iif(t.sum_billable_trades > 0 And t.sum_billable_trades <= 150000, 0.85667,  0.47104))) As r(rate)
  Left Outer Join cte_comm_adj               c On c.Business_Unit_Description = t.Business_Unit_Description
 Order By t.Business_Unit_Description;

I also wouldn't use the same name for the sum just to make it clearer.