Average date difference between two columns

Question

Post reply

Average date difference between two columns

Jake Shelton

Hall of Fame

Points: 3109
More actions
March 6, 2012 at 8:50 am

#252707

Hi all, I've got the datediff per row, but need an average for each column.
So far...
Select PF.ContactID, Datediff (day, StartDate, Lastattended) as AverageTuitionDuration
From PersonalFinance as PF
Inner join PersonalProfiles as PP
on PF.ContactID = PP.ID
Where StartDate < Lastattended
Result set (for the above code)
ContactIDAverageTuitionDuration
52962
531040
54664
55745
Desired Result:
AverageTuitionDuration
805

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

not much given...

Select AVG(Datediff (day, StartDate, Lastattended)) as AverageTuitionDuration

From PersonalFinance as PF

Inner join PersonalProfiles as PP

on PF.ContactID = PP.ID

Where StartDate < Lastattended

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jake Shelton Hall of Fame Points: 3109 More actions · Answer 2

I'm still getting used to all those brackets... 😀

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

CELKO (3/6/2012)
...
Code should be in Standard SQL as much as possible and not local dialect.
...

I completely disagree with this statement. If you are working with MS SQL Server and using the T-SQL extensions provided allows you to develop better performing and scalable code, then you should use it. Same goes if you are using Oracle, PostgreSQL, MySQL, or any other RDBMS system.

The idea that your code should always be 100% portable to another system is not the best way to work. Sorry, but I don't see a lot of companies changing RDBMS systems on a regular basis. Even if you are writing an application like PeopleSoft or JDEdwards, I would have separe code bases for each RDBMS that I would support. The logic between the code would be the same, but the actual code developed would be best for each platform.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

As to the original topic: be careful with the use of DATEDIFF. From your use case, using the DAY unit should be safe. You would start seeing some "rounding" issues if you were to try to average it out in months or some higher unit.

Datediff in this scenario is kind of rounding off the intermediate results, so you're introducing some amount of error into your calculation. (it's actually just counting the unit "boundaries" between the 2 dates.)

declare @startdate datetime

declare @enddate datetime

set @startdate='12/31/2000'

set @enddate='1/1/2001'

select datediff(month,@startdate,@enddate),cast(@enddate-@startdate as int)/(365.25/12)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?