Find the sum of columns depending on the current month in SQL Server 2005

Question

I have the following table. Each row stores a series of values across the columns indicated.

create table MonthlyData (
 Jan int not null,
 Feb int not null,
 Mar int not null,
 Apr int not null,
 May int not null,
 Jun int not null,
 Jul int not null,
 Aug int not null,
 Sep int not null,
 Oct int not null,
 Nov int not null,
 Dec int not null
)

insert into table (3, 1, 3, 4, 5, 6, 7, 8, 9, 4, 3, 2)
.
.
.

What I want to do is, depending on the month of the year, for each row, sum up the values from the first column (Jan) up to and including the column that represents the current month (Aug for example). I suspect that this will probably involve some sort of function that takes the month as the parameter. There will, of course be thousands of rows and not every row will be unique.

I am not quite sure where to start on this or which sql built-in functions/keywords to use for this. Would anyone be able to point me to the right direction?

UPDATE:

Based on Andriy M's solution, I came up with this.

declare @currentMonth int
set @currentMonth = 8
select sum(p1*Jan+p2*Feb+p3*Mar+p4*Apr+
  p5*May+p6*Jun+p7*Jul+p8*Aug+
  p9*Sep+p10*Oct+p11*Nov+p12*Dec) as 'Balance'
from MonthlyData md
cross join MatrixTable mt
where mt.period = @currentMonth

The matrix table has is an identity matrix with the lower left half filled with 1's instead of 0's (column names start with an arbitry prefix, in this case 'p', and followed by a number). An extra column is added at the end to identify each row. The matrix table will be useful for other problems as well in the future, as long as it is sufficiently big.

What is the PK of this table? Also are you open to changing the schema? — Martin Smith, Aug 10 '11 at 22:14
Yes, I am open to changing the schema. I could include an auto-incremented PK. That's not a a problem. — deutschZuid, Aug 10 '11 at 23:13

score 4 · Accepted Answer · edited May 23 '17 at 09:58

4

If the change suggested in my other answer is too radical to adopt, then here's an alternative to @Filip De Vos's solution:

SELECT
  SUM(
    CASE v.number
      WHEN  1 THEN Jan
      WHEN  2 THEN Feb
      WHEN  3 THEN Mar
      WHEN  4 THEN Apr
      WHEN  5 THEN May
      WHEN  6 THEN Jun
      WHEN  7 THEN Jul
      WHEN  8 THEN Aug
      WHEN  9 THEN Sep
      WHEN 10 THEN Oct
      WHEN 11 THEN Nov
      WHEN 12 THEN Dec
    END
  ) AS Total
FROM MonthlyData m
  CROSS JOIN master..spt_values v
WHERE v.type = 'P'
  AND v.number BETWEEN 1 AND MONTH(GETDATE())

The master..spt_values table is a system table used for internal purposes, but can also be used in user queries. One of its subsets contains a list of integers from 0 to 2047, which can be used in many cases as a ready-made tally table.

edited May 23 '17 at 09:58

Community

1
1

answered Aug 10 '11 at 23:33

Andriy M

76,112
17
94
154

Yes, unpivoting is the way to do it. – ErikE Aug 10 '11 at 23:35
@Andriy M. Your answer is very good. It actually inspired me to create a different solution based loosely on this idea. I created a table resembling an identity matrix, except the lower left half of the matrix are all 1's instead of 0's. I added an extra column at the end of the this matrix table called period, which serves as a point of lookup and goes from 1 to n. I then multiply each month column with its corresponding column in the matrix table. So it goes a bit like this (see my original post as comment doesn't allow me to use the at sign) – deutschZuid Aug 11 '11 at 03:12

score 2 · Answer 2 · answered Aug 10 '11 at 23:03

you can use the case statement to set the data you don't want to zero.

declare @current_month int
select @current_month = month(current_timestamp)

select case when @current_month <= 1 then jan else 0 end 
     + case when @current_month <= 2 then feb else 0 end 
     + case when @current_month <= 3 then mar else 0 end 
     + case when @current_month <= 4 then apr else 0 end 
     + case when @current_month <= 5 then may else 0 end 
     + case when @current_month <= 6 then jun else 0 end 
     + case when @current_month <= 7 then jul else 0 end 
     + case when @current_month <= 8 then aug else 0 end 
     + case when @current_month <= 9 then sep else 0 end 
     + case when @current_month <= 10 then oct else 0 end 
     + case when @current_month <= 11 then nov else 0 end 
     + case when @current_month <= 12 then dec else 0 end 
from MonthlyData

score 2 · Answer 3 · answered Aug 10 '11 at 23:24

The structure in your question seems more suitable for final tables used in reporting, in my opinion. As a working table, it would be more convenient with a structure like this:

CREATE TABLE MonthlyData (
  Month int,
  Value int
)

where Month would naturally hold values from 1 to 12. Then summing the data would be very easy:

SELECT
  SUM(Value) AS Total
FROM MonthlyData
WHERE Month BETWEEN 1 AND MONTH(GETDATE())

Find the sum of columns depending on the current month in SQL Server 2005

3 Answers3