Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Maverick_
Posting Yak Master
107 Posts |
Posted - 2014-01-16 : 07:15:19
|
Hi guys,I am trying to figure out how to calculate the overall average number of days taken to complete something.The two fields are enquiry_date (date enquiry is recorded) and complete_date (date enquiry completed/closed).Each enquiry has a enquiry_numberSample data typically looks like:Enquiry number - enquiry_time - complete date1 - 01/01/2014 - 12/01/20142 - 01/01/2014 - 11/01/20143 - 01/01/2014 - 10/01/20144 - 01/01/2014 - 07/01/20145 - 01/01/2014 - 12/01/20146 - 01/01/2014 - 04/01/2014etc.What is the piece of SQL which looks at the average date difference for each enquiry and then sums it all up to give an overall average number of days it takes?I am a bit stuck! |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-16 : 07:17:58
|
[code]SELECT SUM(DATEDIFF(dd,enquiry_time,[complete date]))*1.0/NULLIF(COUNT([Enquiry number]),0) AS AverageEnquiryTimeFROM Table[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Maverick_
Posting Yak Master
107 Posts |
Posted - 2014-01-16 : 10:00:41
|
Thanks Visakh! |
|
|
|
|
|