average of sums

mirthless
mirthless used Ask the Experts™
on
I have a need to create a sum of a set of data that is offset for each column. Then I need to crate an average of each sum.  Here is the data set:

287849      0      0      0      0      0      0      2011-04-01
305175      0      0      0      0      0      0      2011-05-01
321305      0      0      0      0      0      0      2011-06-01
349804      0      0      0      0      0      0      2011-07-01
385270      0      0      0      0      0      0      2011-08-01
396388      0      0      0      0      0      0      2011-09-01
398585      0      0      0      0      0      0      2011-10-01
391251      4856      0      0      0      0      0      2011-11-01
410350      38764      20248      0      0      0      0      2011-12-01
399031      73552      56651      8353      0      0      0      2012-01-01
437927      123331      116539      71708      9266      0      0      2012-02-01
479173      220768      215429      178598      119074      34615      0      2012-03-01
513639      282400      277061      242204      203727      127661      40759      2012-04-01
480104      276591      271252      239102      203081      154021      86797      2012-05-01
344447      182753      179500      154129      122132      81242      55949      2012-06-01
283951      143614      140753      116574      85270      58826      39450      2012-07-01
263782      137062      134201      111321      82022      55578      37887      2012-08-01
231184      131620      129656      106776      79116      53922      36431      2012-09-01
184214      114084      113470      93999      69307      51974      36331      2012-10-01

The left column is my "control" column. Of the remaining right hand columns I need to select and create a avergae of each alternate row summed up. I need this sum for the first three instances of sums. I am not even sure how to explain in words, I can do the work in excel but I need it in a query do I can plot on a report. Here is what I would need from this data set:

(4856+20248+8353+9266+34615+40759)/6 = 19682.83  as neg1
(38764+56651+71708+119074+127661+86797)/6 = 71522.14 as neg2
(73552+116539+178598+203727+154021+55949)/6 = 97798.25 as neg3

Each of these averages need to be unique objects that I can add them to a specific value in the control column. Once I get the averages, I can then add them to the appropriate months value from the control column.

In this specific case I will be adding 19682.83 + 4800104 and
71522.14 + 344447 and
97798.25 + 283951
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
Do these columns have names?
287849      0      0      0      0      0      0      2011-04-01 
305175      0      0      0      0      0      0      2011-05-01 
321305      0      0      0      0      0      0      2011-06-01 
349804      0      0      0      0      0      0      2011-07-01 
385270      0      0      0      0      0      0      2011-08-01 
396388      0      0      0      0      0      0      2011-09-01 
398585      0      0      0      0      0      0      2011-10-01 
391251      4856   0      0      0      0      0      2011-11-01 
410350      38764  20248  0      0      0      0      2011-12-01 
399031      73552  56651  8353   0      0      0      2012-01-01 
437927      123331 116539 71708  9266   0      0      2012-02-01 
479173      220768 215429 178598 119074 34615  0      2012-03-01 
513639      282400 277061 242204 203727 127661 40759  2012-04-01 
480104      276591 271252 239102 203081 154021 86797  2012-05-01 
344447      182753 179500 154129 122132 81242  55949  2012-06-01 
283951      143614 140753 116574 85270  58826  39450  2012-07-01 
263782      137062 134201 111321 82022  55578  37887  2012-08-01 
231184      131620 129656 106776 79116  53922  36431  2012-09-01 
184214      114084 113470 93999  69307  51974  36331  2012-10-01 

Open in new window


Is this data you have in your SQL Server database?
with this table to put your data in


/****** Object:  Table [dbo].[AVtable]    Script Date: 05/22/2012 09:01:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AVtable](
      [ID] [int] NOT NULL,
      [col1] [float] NULL,
      [col2] [float] NULL,
      [col3] [float] NULL,
      [col4] [float] NULL,
      [col5] [float] NULL,
      [col6] [float] NULL,
      [ColDate] [datetime] NULL,
 CONSTRAINT [PK_AVtable] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SELECT T1.ID,(T1.Col1 + T2.col2 + T3.col3 + T4.col4 + T5.col5 + T6.col6) / 6 FROM
(
 (select row_number() OVER(ORDER BY coldate) AS RowN,* from AVtable) T1
JOIN
(select row_number() OVER(ORDER BY coldate) AS RowN,* from AVtable) T2
ON T1.rown = T2.rown -1
JOIN
(select row_number() OVER(ORDER BY coldate) AS RowN,* from AVtable) T3
ON T2.rown = T3.rown - 1
JOIN
(select row_number() OVER(ORDER BY coldate) AS RowN,* from AVtable) T4
ON T3.rown = T4.rown - 1
JOIN
(select row_number() OVER(ORDER BY coldate) AS RowN,* from AVtable) T5
ON T4.rown = T5.rown - 1
JOIN
(select row_number() OVER(ORDER BY coldate) AS RowN,* from AVtable) T6
ON T5.rown = T6.rown - 1
)

Open in new window

Most Valuable Expert 2012
Top Expert 2008

Commented:
Sometimes it is easier to calculate in the application, and sometimes it is easier in the database.  I can't see where this is being used, so it is difficult to suggest anything.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2014

Commented:
context is king

Author

Commented:
Sorry...I am running this in a stored procedure...then will be attaching the data source to an SSRS report. I can name the columns anything I want as it is simply a temp table within the procedure. I have a good portion of code that has crunched financial data to place it into monthly totals. Each row is a monthly total with each column being for different period of time. I am plotting a forecasted change based on historical data.
Top Expert 2014

Commented:
ok.  I asked about the column names in order to better understand what you are trying to do (context).

Let me ask this a different way...
Please explain the context or reasoning behind summing data from different controls across time?

I've the nagging suspicion that you have boxed yourself into a corner with your prior data manipulation.

==========
Possible solution paths:
* traverse the data with a cursor
* copy the first 9 rows to a temp table and the iterate the temp table three times, peeling off the top non-zero values each iteration of the columns.  During the iteration, the top/first non-zero value in each is updated to a zero in preparation for the next iteration.
Most Valuable Expert 2012
Top Expert 2008

Commented:
SSRS has functions that you can use or create to sum for a column:

Example:

=Sum(Fields!TotalAmount.Value, "MyDataSet")

Open in new window

Author

Commented:
Just what I needed.

Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial