March 6, 2012 at 8:50 am
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
March 6, 2012 at 8:54 am
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
March 6, 2012 at 9:13 am
I'm still getting used to all those brackets... 😀
Thanks
March 6, 2012 at 10:39 am
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.
March 6, 2012 at 11:01 am
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?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply