CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Davethebfg
Davethebfg

Reputation: 203

Calculations over Multiple Rows SQL Server

If I have data in the format;

Account  | Period     | Values
Revenue  | 2013-01-01 | 5432
Revenue  | 2013-02-01 | 6471
Revenue  | 2013-03-01 | 7231
Costs    | 2013-01-01 | 4321
Costs    | 2013-02-01 | 5672
Costs    | 2013-03-01 | 4562

And I want to get results out like;

Account  | Period     | Values
Margin   | 2013-01-01 | 1111
Margin   | 2013-02-01 |  799
Margin   | 2013-03-01 | 2669
M%       | 2013-01-01 |  .20
M%       | 2013-02-01 |  .13
M%       | 2013-03-01 |  .37

Where Margin = Revenue - Costs and M% is (Revenue - Costs)/Revenue for each period.

I can see various ways of achieving this but all are quite ugly and I wanted to know if there was elegant general approach for these sorts of multi-row calculations.

Thanks

Edit

Some of these calculations can get really complicated like

Free Cash Flow = Margin - Opex - Capex + Change in Working Capital + Interest Paid

So I am hoping for a general method that doesn't require lots of joins back to itself.

Thanks

Upvotes: 2

Views: 6901

Answers (4)

Bill Gregg
Bill Gregg

Reputation: 7147

Ok, then just Max over a Case statement, like such:

with RevAndCost as (revenue,costs,period)
as
(

    select "Revenue" = Max(Case when account="Revenue" then Values else null end),
           "Costs" = MAX(Case when account="Costs" then values else null end),
           period
            from data
    group by period

)

select Margin = revenue-costs,
       "M%" = (revenue-costs)/nullif(revenue,0)
from RevAndCost

Upvotes: 2

Beth
Beth

Reputation: 9607

I'd do it like this:

SELECT r.PERIOD, r.VALUES AS revenue, c.VALUES AS cost,
r.VALUES - c.VALUES AS margin, (r.VALUES - c.VALUES) / r.VALUES AS mPct
FROM 
    (SELECT PERIOD, VALUES FROM t WHERE
    ACCOUNT = 'revenue') r INNER JOIN
    (SELECT PERIOD, VALUES FROM t WHERE
    ACCOUNT = 'costs') c ON
    r.PERIOD = c.PERIOD

Upvotes: 1

Bill Gregg
Bill Gregg

Reputation: 7147

Here I use a Common Table Expression to do a full outer join between two instances of your data table to pull in Revenue and Costs into 1 table, then select from that CTE.

with RevAndCost as (revenue,costs,period)
as
(
    select  ISNULL(rev.Values,0) as revenue, 
            ISNULL(cost.values,0) as costs, 
        ISNULL(rev.period,cost.period)
    from data rev full outer join data cost
    on rev.period=cost.period
)

select Margin = revenue-costs,
   "M%" = (revenue-costs)/nullif(revenue,0)
from RevAndCost

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146409

Use a full self-join with a Union

Select 'Margin' Account, 
   coalesce(r.period, c.period) Period,
   r.Values - c.Values Values
From myTable r
   Full Join Mytable c
      On c.period = r.period
Union
Select 'M%' Account, 
   coalesce(r.period, c.period) Period,
   (r.Values - c.Values) / r.Values Values
From myTable r
   Full Join Mytable c
      On c.period = r.period

Upvotes: 1

Related Questions