?
Solved

SSRS Weighted Average OF DATES

Posted on 2012-08-24
7
Medium Priority
?
1,114 Views
Last Modified: 2012-09-04
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
Comment
Question by:Josh2442
  • 5
  • 2
7 Comments
 

Author Comment

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

Expert Comment

by:Craig Yellick
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

by:Josh2442
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

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

Accepted Solution

by:
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

by:Craig Yellick
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

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

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.

Join & Ask a Question