Wrong Calculation 2

[TariqMehmod]

Sir while using SQLSERVER 2012 I have these codes

[pre]
select top 1 reading,emp_start,emp_end from bin1_final order by date

SELECT top 1 reading,(datediff(second,emp_start,emp_end)) as x, 3600 as y from bin1_final

SELECT top 1 (reading/(datediff(second,emp_start,emp_end))*3600) as y from bin1_final
[/pre]

The result looks like this

Please help

 

[strongm]

Possibly because TOP without an Order By clause returns the first N number of rows in an undefined order …, as Microsoft say: In a SELECT statement, always use an ORDER BY clause with the TOP clause. Because, it's the only way to predictably indicate which rows are affected by TOP.

 

[TariqMehmod]

Sir this time I used like this

SELECT top 1 (reading/(datediff(second,emp_start,emp_end))*3600) as y from bin1_final order by date

But same result

 

[strongm]

Same result? So the three queries are giving the exact same results as in your first post? i.e we are getting a different 'reading' value when we should be getting the same one for each query?

 

[MarkSweetland]

Datediff returns an integer value. Your division is performing an integer operation and truncating the decimal portion. Convert to a decimal data type:

SELECT top 1 (reading/convert(decimal(12,2), (datediff(second,emp_start,emp_end)))*3600) as y from bin1_final

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[Olaf Doschke]

Why don't you ask SQL Server?

SELECT 50135/17808
SELECT 50135/17808*3600
SELECT 50135*3600/17808

See? SQL Server stays with the original types of values rather than guessing a good type for the result, so a solution is to use CONVERT, The last of my SELECTs also shows you can get a better integer result, when you turn the formula around. It's possible to stay exact first multiplying and then only get a slight error when dividing as last step.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name