CODEWITHSUNDEEP
sql-serversql-server-2005t-sql
deutschZuid
deutschZuid

Reputation: 1058

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

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.

Upvotes: 2

Views: 1163

Answers (3)

Andriy M
Andriy M

Reputation: 77657

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.

Upvotes: 4

Andriy M
Andriy M

Reputation: 77657

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())

Upvotes: 2

Filip De Vos
Filip De Vos

Reputation: 11908

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

Upvotes: 2

Related Questions