By ratanmalko


2019-05-15 15:56:48 8 Comments

I have the following Excel calculation that I want to replicate to a T-SQL statement: example

This Excel is imported to a SQL table (SQL Server 2012) in the same format with two additional columns for plant and year.

Below is my SQL view that I use for reporting. As you can see the months became rows and the value-columns rows:

+---------+------+-------+------+--------+
|  Plant  | Year | Month |  A   |   B    |
+---------+------+-------+------+--------+
| Plant X | 2019 |     1 |  526 | 16     |
| Plant X | 2019 |     2 |  786 | 54     |
| Plant X | 2019 |     3 | 1800 | 85     |
| Plant X | 2019 |     4 | 9780 | 15     |
| Plant X | 2019 |     5 | 9780 | (null) |
| Plant Y | 2019 |     1 | 4080 | 128    |
| Plant Y | 2019 |     2 | 1387 | 179    |
+---------+------+-------+------+--------+

Here is the Fiddle for above sql view.

CREATE TABLE Test
    ([Plant] varchar(13), [Year] int, [Month] int, [A] decimal(18,2), [B] decimal(18,2))
;

INSERT INTO Test
    ([Plant], [Year], Month, A, B)
VALUES
    ('Plant X', 2019,1,526 ,16 ),
    ('Plant X', 2019,2,786 ,54),
    ('Plant X', 2019,3, 1800,85),
    ('Plant X', 2019,4, 9780,15),
    ('Plant X', 2019,5, 9780,NULL),
    ('Plant Y', 2019,1,4080 ,128 ),
    ('Plant Y', 2019,2,1387 ,179)
;

Is it possible to do the calculation in my SQL view as depicted in above Excel image? If yes, can you show an example?

Since I also have the data in an SQL table just like the Excel structure (with months as columns) would that make more sense to do the calculation in that format?

Thank you.

3 comments

@Xabi 2019-05-15 20:32:50

In a fast way and with your sample table, it could be like this:

select t.*
    , Calculated = (
        select cast(round(sum(B) * 1000000 / sum(A), 0) as int)
        from Test
        where Plant = t.Plant
            and [Year] = t.[Year]
            and [Month] <= t.[Month]
        having sum(A) <> 0
        )
from Test t

@ratanmalko 2019-05-16 12:24:12

this works perfectly for me. Thanks alot!

@Faisal Mehboob 2019-05-15 16:53:44

You can also achieve this by using PIVOT and UNPIVOT in TSQL.

SELECT [Plant]
  ,[Year]
  ,[newColumn]
  ,SUM([1]) [1]
  ,SUM([2]) [2]
  ,SUM([3]) [3]
  ,SUM([4]) [4]
  ,SUM([5]) [5]
FROM [Test]
UNPIVOT(Orders FOR newColumn IN (
          A
         ,B
        )) AS unpvt
PIVOT(SUM([Orders]) FOR [Month] IN (
         [1]
        ,[2]
        ,[3]
        ,[4]
        ,[5]
        )) AS PivotTable
GROUP BY [Plant]
  ,[Year]
  ,[newColumn]

@Luis Cazares 2019-05-15 16:22:52

This is a problem of having to unpivot some columns and pivot on another one. To unpivot, I use a CROSS APPLY and a table values constructor which is very versatile (unlike the unpivot operator). I also use the cross tabs method to pivot data.

SELECT  Plant, [Year], colName,
    SUM(CASE WHEN [Month] = 1 THEN colValue END),
    SUM(CASE WHEN [Month] = 2 THEN colValue END),
    SUM(CASE WHEN [Month] = 3 THEN colValue END),
    SUM(CASE WHEN [Month] = 4 THEN colValue END),
    SUM(CASE WHEN [Month] = 5 THEN colValue END),
    SUM(CASE WHEN [Month] = 6 THEN colValue END),
    SUM(CASE WHEN [Month] = 7 THEN colValue END)
FROM Test
CROSS APPLY( VALUES( 'A', A),( 'B', B)) AS x(colName, colValue)
GROUP BY Plant, [Year], colName
ORDER BY Plant, [Year], colName;

Related Questions

Sponsored Content

15 Answered Questions

[SOLVED] Create a date from day month and year with T-SQL

13 Answered Questions

[SOLVED] MySQL Query GROUP BY day / month / year

7 Answered Questions

3 Answered Questions

1 Answered Questions

Return all rows grouped by year and month from date SQL

1 Answered Questions

Malfunction of data copy from excel to Sql using BulkCopy (vb.net)

2 Answered Questions

[SOLVED] Get datetime filteration rows in SQL by using month and year

3 Answered Questions

[SOLVED] Combining Multiple SQL Views ON Year & Month

  • 2014-01-13 14:33:20
  • PJW
  • 61 View
  • 0 Score
  • 3 Answer
  • Tags:   sql

1 Answered Questions

[SOLVED] SQL Year to Date report involving 3 tables

4 Answered Questions

Calculating and showing Age Column on Sql Server as (xx Years, xx months)

  • 2012-03-16 08:35:35
  • EngAbth9
  • 1908 View
  • 1 Score
  • 4 Answer
  • Tags:   c# sql

Sponsored Content