locked
Calculate AVG on two dates RRS feed

  • Question

  • Hi,

    I would like to calculate AVG on the  difference of two Dates

    I have two dates Columns as follows:

    - Col1 (Date1)

    - Col2 (Date2)

    then , i tried to make a query as follows

    Select Avg(DateDiff(day,col1,ol2)) as difference

    from table1

    this will give me an error ..

    so,

    what's the correct way to find the Avg of the difference of two dates?

    Wednesday, November 14, 2012 1:01 PM

Answers

  • Hi Faisal,

    If you trying to use this column in report then you can use Datediff function in report itself instead of calculating in the query, below is the expression to be used for displaying the difference of dates.

    =datediff(DateInterval.Day,cdate(Fields!Date.Value),cdate(Fields!Date1.Value)) , as per your requirement you can change DateInterval to month, year etc ..

    output will be like as below

    which should resolve your requirement.

    Cheers,

    Arun Gangumalla

    Please mark as answered if it resolves your issue or helpful.

    Thursday, November 15, 2012 6:19 AM

All replies

  • What error, it should be working

    Select Avg(DateDiff(day,col1,col2)) as difference

    from table1

    I think you missed a character 'c' for col2, no?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance


    Wednesday, November 14, 2012 1:14 PM
  • I would like to calculate AVG on the  difference of two Dates

    It's unclear to me what your expected results are.  The query you posted will return the average difference of col1 and col2 for the entire table, expressed in days.

    If you want to compute the difference in days for each row, just omit the AVG function.

    If you want to compute the midpoint of the date range for each row, add a DATEADD:

    SELECT DATEADD(day, DATEDIFF(day, col1, col2)/2, col1) AS difference
    FROM dbo.table1;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Wednesday, November 14, 2012 1:18 PM
  • I just want to calculate Average Difference between two dates in (Days)

    When i subtracted col1-col2

    it will give me the number of days between two dates , so basically the difference column providing me the number of days,

    now, i want to calculate the average of new column (i.e: Difference)

    in-short i need AVG(Difference)

    hope this will make some understanding..

    Wednesday, November 14, 2012 1:24 PM
  • In that case, the scrupt you posted should work.  You mentioned an error...what error are you getting?

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Thursday, November 15, 2012 3:07 AM
  • Hi Faisalkyo,

    Just as Dan said, the query you used will return a column ”Difference” with a single value of average difference date for entire table.

    I try to reproduce the issue in my test environment with a similar table, however, it works well. Please share the error message with us, so we can help to work out this issue.

    Or, you can try the following query to redesign the report:
           SELECT col1,col2, DateDiff(day,col1,col2) as difference
           FROM TABLE
    And then, in the report design surface, add a new row to the Tablix, and specify the value with following expression:
    =Avg(Fields!difference.Value).

    For more information, please see:
    Avg Function (Reporting Services)

    Regards,
    Fanny Liu


    Fanny Liu

    TechNet Community Support

    Thursday, November 15, 2012 3:32 AM
  • Hi Faisal,

    If you trying to use this column in report then you can use Datediff function in report itself instead of calculating in the query, below is the expression to be used for displaying the difference of dates.

    =datediff(DateInterval.Day,cdate(Fields!Date.Value),cdate(Fields!Date1.Value)) , as per your requirement you can change DateInterval to month, year etc ..

    output will be like as below

    which should resolve your requirement.

    Cheers,

    Arun Gangumalla

    Please mark as answered if it resolves your issue or helpful.

    Thursday, November 15, 2012 6:19 AM
  • Thanks to All members
    Thursday, November 15, 2012 6:35 AM