SQL Server Table Calculations

Question

Given:

+-----------+---------------+-------------+-------+
|   Name    |   Item        | Year        | Value |
+-----------+---------------+-------------+-------+
| Company A | Sales         | 2017        |   100 |
| Company A | Sales         | 2016        |   100 |
| Company A | Sales         | 2015        |   400 |
| Company A | Profit        | 2017        |    50 |
| Company A | Profit        | 2016        |    50 |
| Company A | Profit        | 2015        |   200 |
| Company B | Sales         | 2017        |   200 |
| Company B | Sales         | 2016        |   100 |
| Company B | Profit        | 2017        |    20 |
| Company B | Profit        | 2016        |    20 |
+-----------+---------------+-------------+-------+

How do I use SQL to transform it to:

+----------+---------------+--------+-----------+
|   Name   |   Year        | Margins| 2 yr Ave  |
+----------+---------------+--------+-----------+
| CompanyA | 2015          |    50% |    NULL   |  
| CompanyA | 2016          |    50% |    50%    |  
| CompanyA | 2017          |    50% |    50%    |  
| CompanyB | 2016          |    20% |    NULL   |
| CompanyB | 2017          |    10% |    15%    |
+----------+---------------+--------+-----------+

and

+----------+---------------+--------+-----------+
|   Name   |   Year        | CompA  | CompB     |
+----------+---------------+--------+-----------+
| Margin   | 2015          |    50% |    NULL   |  
| Margin   | 2016          |    50% |    20%    |  
| Margin   | 2017          |    50% |    10%    |  
| 2Yr Ave  | 2015          |   NULL |    NULL   |
| 2Yr Ave  | 2016          |    50% |    NULL   |
| 2Yr Ave  | 2017          |    50% |    15%    |
+----------+---------------+--------+-----------+

Margin: (profit/sales*100) of a given year and
2 Year Ave: Margin this year + Margin of previous year / 2, NULL if there is no data for previous year.

You're asking for a fairly complex query. Have you tried something yet? — Tim Biegeleisen, Aug 18 '17 at 05:48
I'm new to SQL... I am stuck, I am only able to put 1 year in my where statement and pick out 1 year at a time. — Marc, Aug 18 '17 at 05:49

Tim Biegeleisen · Accepted Answer · 2017-08-18T06:06:08.623

My approach would be to first aggregate once over your table using GROUP BY to compute the profit margins for each company in each year. Then, make a second pass using LAG() to calculate the recent two year margin average.

WITH cte AS (
    SELECT
        Name,
        Year,
        100*MAX(CASE WHEN Item = 'Profit' THEN Value END) /
            MAX(CASE WHEN Item = 'Sales' THEN Value END) AS Margins
    FROM yourTable
    GROUP BY
        Name,
        Year
)

SELECT
    Name,
    Year,
    Margins,
    (Margins + LAG(Margins) OVER (PARTITION BY Name ORDER BY Year)) / 2 AS [2 yr Avg]
FROM cte
ORDER BY
    Name,
    Year

For your second table output, you can just pivot the result from this query. Place the code I gave you into a CTE, and then pivot on the margin columns.

Output:

Demo here:

Rextester

score 0 · Answer 2 · answered Aug 18 '17 at 05:54

As at this point you didn't include any attempt on your side, I'll just post an idea to show you one possible approach.

SELECT T1.Name, T1.Year, Margins = 100 * T2.Value / T1.Value
FROM TBL1 T1
INNER JOIN TBL1 T2 ON T1.Name = T2.Name AND T1.Year = T2.Year AND T1.Item = 'Sales' AND T2.Item = 'Profit'
GROUP BY T1.Name, T1.Year

What I'm doing is joining the table in order to have the values you need in the same row, so you can then easily do the calculation.

This way you get the first result table without the average. Then I would use this result as a source for a second query to calculate the average, also joining the table with itself.

Your second result table is the same thing as the first one but transposed. If you search for that you will find plenty of information, like in here.

SQL Server Table Calculations

2 Answers2

Rextester