Calculating Margin in CASE Statement and GROUP BY Clause

Question

I think I am running into an error in how I have chosen to aggregate my columns in a group by statement.

I am working out Margin % and grouping into months using a case statement like so:

SUM(CASE WHEN MONTH(DateProcessed) = 1 AND Revenus <> 0 THEN (Revenue - Cost) / Revenue ELSE 0 END AS [January]

So my equation ends up as: SUM( (Revenue - Cost) / Revenue )

Which in months with multiple transactions gives me the wrong value

I am sure my equation needs to be: SUM(Revenue - Cost) / SUM(Revenue)

But I cannot format my case statement like that. How would you work out this sort of equation?

Thanks,

Answer 1

I don't have your full schema, but this should work for you.

;WITH CTE_Summary AS
(
    SELECT MONTH(DateProcessed) AS MonthProcessed
        , SUM(ISNULL(Revenue, 0.0)) AS RevenueSum
        , SUM(ISNULL(Cost, 0.0)) AS CostSum
    FROM <TABLE NAME>
    GROUP BY MONTH(DateProcessed)
)
SELECT sum(CASE WHEN MonthProcessed = 1 THEN (RevenueSum - CostSum) / RevenueSum ELSE 0.0 END) AS [January]
FROM CTE_Summary

Added the sum function.

Answer 2

Here's one way.

select
 (select (sum(Revenue) - sum(Cost)) / sum(Revenue) from tmp where month(DateProcessed) = 1) as jan
,(select (sum(Revenue) - sum(Cost)) / sum(Revenue) from tmp where month(DateProcessed) = 2) as feb
,(select (sum(Revenue) - sum(Cost)) / sum(Revenue) from tmp where month(DateProcessed) = 3) as mar

If using the common table expression approach and more than a month is expected or included then a sum will be needed.

;with CTE_summary as (
    select
         month(DateProcessed) as monthProcessed
        ,sum(isnull(Revenue, 0.0)) as Revenuesum
        ,sum(isnull(Cost, 0.0)) as Costsum
    from tmp
    where year(dateprocessed) = 2015
    group by month(DateProcessed)
)
select
     sum(case when monthProcessed = 1 then (Revenuesum - Costsum) / Revenuesum else 0.0 end) as [jan]
    ,sum(case when monthProcessed = 2 then (Revenuesum - Costsum) / Revenuesum else 0.0 end) as [feb]
    ,sum(case when monthProcessed = 3 then (Revenuesum - Costsum) / Revenuesum else 0.0 end) as [mar]
    --,...
from CTE_summary