locked
how to display avg (date diff of days) as a separate column RRS feed

  • Question

  • Hello,

    i have a display question with the below data

    how to display average date difference with results either horizontally or vertically (provided below) please look into it,

     
     create table student
     (sno numeric, sname varchar(20),dob datetime,jondate datetime);

    insert into Student values (1,'AAA','08/02/2013','10/20/2013')
    insert into Student values (2,'BBB','08/02/2013','09/28/2013')
    insert into Student values (3,'BB1','08/02/2013','08/28/2013')
    insert into Student values (4,'BB2','08/02/2013','09/18/2013')
    insert into Student values (5,'BB3','08/02/2013','09/11/2013')


    insert into Student values (6,'AAA','07/02/2013','08/20/2013')
    insert into Student values (7,'BBB','07/02/2013','08/28/2013')
    insert into Student values (8,'BB1','07/02/2013','06/28/2013')
    insert into Student values (9,'BB2','07/02/2013','06/18/2013')
    insert into Student values (10,'BB3','07/02/2013','08/11/2013')

    insert into Student values (11,'AAA','03/02/2013','10/20/2013')
    insert into Student values (12,'BBB','02/02/2013','09/28/2013')
    insert into Student values (13,'BB1','02/02/2013','08/28/2013')
    insert into Student values (14,'BB2','02/02/2013','09/18/2013')
    insert into Student values (15,'BB3','02/02/2013','09/11/2013')

    insert into Student values (16,'CCC','05/23/2013','07/16/2013')
    insert into Student values (17,'DDD','05/03/2013','08/11/2013')
    insert into Student values (18,'EEE','05/02/2013','06/01/2013')
    insert into Student values (19,'FFF','05/02/2013','05/20/2013')

    select dob, jondate, DATEDIFF (dd,dob,jondate) DayCount from student  s
    order by 1,2

    Select dob,AVG(DATEDIFF (dd,dob,jondate)) AvgDays  from student s1  group by dob
    order by 1,2


    How to write a query to display avgDays column in the above first query results for ex:

    dob      jondate     DayCount AvgDays
    2013-02-02 00:00:00.000 2013-08-28 00:00:00.000 207   223
    2013-02-02 00:00:00.000 2013-09-11 00:00:00.000 221
    2013-02-02 00:00:00.000 2013-09-18 00:00:00.000 228
    2013-02-02 00:00:00.000 2013-09-28 00:00:00.000 238
    2013-03-02 00:00:00.000 2013-10-20 00:00:00.000 232   232
    2013-05-02 00:00:00.000 2013-05-20 00:00:00.000 18   24
    2013-05-02 00:00:00.000 2013-06-01 00:00:00.000 30
    2013-05-03 00:00:00.000 2013-08-11 00:00:00.000 100   100
    2013-05-23 00:00:00.000 2013-07-16 00:00:00.000 54   54 
    2013-07-02 00:00:00.000 2013-06-18 00:00:00.000 -14   25
    2013-07-02 00:00:00.000 2013-06-28 00:00:00.000 -4
    2013-07-02 00:00:00.000 2013-08-11 00:00:00.000 40
    2013-07-02 00:00:00.000 2013-08-20 00:00:00.000 49
    2013-07-02 00:00:00.000 2013-08-28 00:00:00.000 57
    2013-08-02 00:00:00.000 2013-08-28 00:00:00.000 26   49
    2013-08-02 00:00:00.000 2013-09-11 00:00:00.000 40
    2013-08-02 00:00:00.000 2013-09-18 00:00:00.000 47
    2013-08-02 00:00:00.000 2013-09-28 00:00:00.000 57
    2013-08-02 00:00:00.000 2013-10-20 00:00:00.000 79

    OR something like below

    dob jondate DayCount
    2013-02-02 00:00:00.000 2013-08-28 00:00:00.000 207
    2013-02-02 00:00:00.000 2013-09-11 00:00:00.000 221
    2013-02-02 00:00:00.000 2013-09-18 00:00:00.000 228
    2013-02-02 00:00:00.000 2013-09-28 00:00:00.000 238
    'Average'             223
    2013-03-02 00:00:00.000 2013-10-20 00:00:00.000 232
    'Average'             232
    2013-05-02 00:00:00.000 2013-05-20 00:00:00.000 18
    2013-05-02 00:00:00.000 2013-06-01 00:00:00.000 30
    2013-05-03 00:00:00.000 2013-08-11 00:00:00.000 100
    'Average'             24
    2013-05-23 00:00:00.000 2013-07-16 00:00:00.000 54
    'Average'             54
    2013-07-02 00:00:00.000 2013-06-18 00:00:00.000 -14
    2013-07-02 00:00:00.000 2013-06-28 00:00:00.000 -4
    2013-07-02 00:00:00.000 2013-08-11 00:00:00.000 40
    2013-07-02 00:00:00.000 2013-08-20 00:00:00.000 49
    2013-07-02 00:00:00.000 2013-08-28 00:00:00.000 57
    'Average'             25
    2013-08-02 00:00:00.000 2013-08-28 00:00:00.000 26
    2013-08-02 00:00:00.000 2013-09-11 00:00:00.000 40
    2013-08-02 00:00:00.000 2013-09-18 00:00:00.000 47
    2013-08-02 00:00:00.000 2013-09-28 00:00:00.000 57
    2013-08-02 00:00:00.000 2013-10-20 00:00:00.000 79
    'Average'             49

    is this possible i am using sql server 2008 R2

    Thanks in advance

    Asitti

    Friday, June 28, 2013 2:59 PM

Answers

  • select
    	dob
    	, jondate
    	, DATEDIFF (dd,dob,jondate) Daycnt
    	, AVG(DATEDIFF (dd,dob,jondate)) Over (partition by DOB )  AvgDays
    From	
    	Student

    Not returning your values, but I think that it's close to what you want.
    Friday, June 28, 2013 4:17 PM

All replies

  • select
    	dob
    	, jondate
    	, DATEDIFF (dd,dob,jondate) Daycnt
    	, AVG(DATEDIFF (dd,dob,jondate)) Over (partition by DOB )  AvgDays
    From	
    	Student

    Not returning your values, but I think that it's close to what you want.
    Friday, June 28, 2013 4:17 PM
  • Hello Tim,

    Thank you so much, it is what i exactly needed, buti n addition i have two quick questions

    is there any way to remove duplicates in the avgdays column (i.e., show average only once per dob change rather )

    another question why there is no group by clause, but it is giving results with out any error

    please tell me

    Thanks in advance

    asiti

    Friday, June 28, 2013 4:33 PM
  • The partition shows the aggregate result without having to aggregate - it's one of the beauties of process and allows you to show the individual results over the entire set of details.

    With Student_cte As
    (
    select
    	dob
    	, jondate
    	,DATEDIFF (dd,dob,jondate) Daycnt
    	, AVG(DATEDIFF (dd,dob,jondate)) Over (partition by DOB )  AvgDays
    	, row_number() Over (partition by DOB order by DOB)  RN
    From	
    	#student
    )
    
    Select
    	dob
    	, jondate
    	, Daycnt
    	, Case	
    		When Rn =1 Then Convert(varchar(10),AvgDays)
    		Else ''
    	End AvgDays
    From 
    	Student_cte

    Worrying too much about presentation is problematic in SQL - that should be a presentation layer concern in Excel or SSRS, but the about should get you closer.

    And while on the topic, what's up with DOB?  Whatever.

    Good luck,

    tim

    Friday, June 28, 2013 9:46 PM