SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Compute Column		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sonjan
Starting Member

Australia
21 Posts
Posted - 08/19/2012 :  23:35:44  Show Profile  Reply with Quote
Hi

I have some code in a SQL report which I want to transfer to TSQL so I can create a view. Aim is to calculate no of kilometres per year. Report code is:

Function AnnualisedKilometres(StartDate As DateTime, EndDate As DateTime, StartOdometer As Double, EndOdometer As Double) As Double

Dim KilometresPerYear As Double
Dim Percentage As String

KilometresPerYear = ((EndOdometer - StartOdometer) / (EndDate.Subtract(StartDate).Days)) * 365

AnnualisedKilometres = Math.Round(KilometresPerYear)

TSQL code is:

(max_fueltrans.odometer) - (min_fueltrans.odometer) / datediff(dd,@Enddate,@StartDate) as KilometresPerYear

Not correct as odometer column is float and variable is datetime.

Any assistance much appreciated
Regards

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 08/19/2012 :  23:49:55  Show Profile  Reply with Quote
what do you mean by not correct? are you not getting intended result?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

sonjan
Starting Member

Australia
21 Posts
Posted - 08/20/2012 :  00:02:25  Show Profile  Reply with Quote
Correct - I'm not getting the intended result. The results I retrieve are:

maxodometer = 45756
minodometer = 44571
kmsinperiod = 1185
annualkms = 44318.2258064516 (incorrect)
DiffinDays = 31
mintransdate = 01/01/2012
maxtransdate = 23/01/2012

Annual Km's should be 40,448
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 08/20/2012 :  07:09:41  Show Profile  Reply with Quote
I may not have followed your arithmetic completely, but it seems to me that the calculation would be as follows:
Kilometers in period = 1185
Days in period       = 23 (inclusive)
average km per day   = 1185/24 = 51.52;
Annual kms           = 365*51.52 = 18,805
If that is the case, you can calculate it in T-SQL like shown below; simply replace the hard-coded odometer readings and dates that I have in there with the column names or variables.
SELECT	365*(45756-44571)/ (1.0+DATEDIFF(dd,'20120101','20120123'))
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/20/2012 :  07:43:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Your paranthesises are wrong. You are only dividing the MIN-value with the number of days.
Also, the DATEDIFF function will need your dates to be in ascending order to return a positive day count.


T-SQL =
365.2425E * (@OdometerEnd - @OdometerStart) / (1E + DATEDIFF(DAY, @StartDate, @EndDate)) AS KilometresPerYear


N 56°04'39.26"
E 12°55'05.63"
Edited by - SwePeso on 08/20/2012 07:44:12
Go to Top of Page

sonjan
Starting Member

Australia
21 Posts
Posted - 08/21/2012 :  18:25:02  Show Profile  Reply with Quote
In trying to project an annual usage based on a rate calculated from two random dates ie
No of kms / no of days * days per year (making no of days per year 365.25 to average out leap years) the following syntax worked correctly:

(((max_fueltrans.odometer) - (min_fueltrans.odometer)) / (datediff(dd,@StartDate,@Enddate))) * 365.25) as annualkms

Thanks everyone for your input, it helped a great deal.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000