Solved

# SSRS Weighted Average OF DATES

Posted on 2012-08-24
Medium Priority
1,114 Views
Hi I was wondering how I could calculate the weighted average or dates and display it in my report.
Id Amount   Date
1.  500.        2/3/2012
500.        2/4/2012
500.       2/5/2012
500.       2/6/2012
500.       2/7/2012
2.   500.       2/4/2012
500.      2/3/2012
500.      2/8/2012
500.      2/12/2012
3.   500.       2/9/2012
500.     2/10/2012
500.      2/15/2012
500.      2/17/2012
500.      2/19/2012
Given above is a sample data.
Also could you teach me how to just grab the date average in SSRS?

When I do
=avg(fields!date.value) it displays error
0
Question by:Josh2442
• 5
• 2

Author Comment

ID: 38329850
I am not working on 2008 r2 and it's just SSRS 2008
0

LVL 11

Expert Comment

ID: 38330246
There are two definitions of "average". The mean is available via the AVG() function and is defined as "sum of the values divided by the count". Dates cannot be added together so that's why you get an error.  The other definition of average is the median, which means the middle of a sorted set.  Dates can be sorted, so you could get the median of a list of dates.

Given your example, what do you expect to see as the "average" dates? In the case of ID=1, you could determine the median as 2/5/2012 because its the middle date in the sorted sequence of dates.

As for a weighted median, not sure how you'd manage that. You'd have to somehow multiply a date by a weighting factor.

Median is not a built-in SSRS function. Here's an article on how to accomplish it in a narrow context.

http://techfilth.blogspot.com/2008/07/calculate-median-on-group-in-ssrs.html
0

Author Comment

ID: 38330580
CREATE TABLE #abc (abc DATE)

INSERT INTO #abc (abc)
(SELECT '2/3/2012'  AS abc
UNION ALL
SELECT '2/4/2012'  AS abc
UNION ALL
SELECT '2/5/2012'  AS abc
UNION ALL
SELECT '2/6/2012'  AS abc
UNION ALL
SELECT '2/7/2012' AS abc
)

SELECT CAST(AVG(CAST(CAST(LEFT(abc, 12) as datetime) AS FLOAT)) AS DATETIME) FROM #abc

To perform an AVG operation on a DATETIME, you must first convert the DATETIME to a decimal value, perform the aggregation, then cast back.
0

Author Comment

ID: 38330589
Thanks Craig, I just played and got this
0

Accepted Solution

Josh2442 earned 0 total points
ID: 38330619
Here's the breakdown--

--convert into datetime--
SELECT CAST (abc AS DATETIME) FROM #abc

--then get into decimal--
SELECT CAST(CAST (abc AS DATETIME)  AS FLOAT )FROM #abc

--then do agregation---
SELECT avg (CAST(CAST (abc AS DATETIME)  AS FLOAT ))FROM #abc

--then convert into datetime----
SELECT CAST(avg (CAST(CAST (abc AS DATETIME)  AS FLOAT )) AS DATETIME)FROM #abc

resultset--
2012-02-05 00:00:00.000
0

LVL 11

Expert Comment

ID: 38330710
That's a side-effect of the way SQL Server stores dates, but it does the trick to get the mean. Good job.
0

Author Closing Comment

ID: 38362829
It later worked just te way I wanted it too
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differeâ€¦
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month9 days, 7 hours left to enroll

#### 621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.