Compute Column - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Compute Column

Author

Topic

sonjan
Starting Member

22 Posts

Posted - 2012-08-19 : 23:35:44

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

52326 Posts

Posted - 2012-08-19 : 23:49:55

what do you mean by not correct? are you not getting intended result?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sonjan
Starting Member

22 Posts

Posted - 2012-08-20 : 00:02:25

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 07:09:41

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'))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-20 : 07:43:29

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"

sonjan
Starting Member

22 Posts

Posted - 2012-08-21 : 18:25:02

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources