Solved

# SSRS Weighted Average OF DATES

Posted on 2012-08-24
1,017 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

Author Comment

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

LVL 11

Expert Comment

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

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

Thanks Craig, I just played and got this
0

Accepted Solution

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

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

It later worked just te way I wanted it too
0

## Featured Post

### Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.