Problem With Round Function In TSQL

Feb 13, 2008

Trying below instructions

create table t

(


indexvalue float

)
go

insert t

values (109.1)

insert t

values (109.3)

insert t

values (109.5)

insert t

values (109.9)

go
select *

from t


select sum (indexvalue) / count (*)

from t


select round (sum(indexvalue)/count (*), 1) -- this line is result of round (109.45) shows 109.4 that is incorrect
from t --why round function doesn't work correctly in this select


select round (109.45, 1) -- but this line results 109.5 that is correct


View 3 Replies


ADVERTISEMENT

Round Function

Jun 18, 2008

I have a money field, which I want to Round price in it to nearest .95 cent . The problem I have is, I don't know how to give 0.95 to Round function.

Thanks
Mazdak

View 1 Replies View Related

Strange ROUND Function!!!

Feb 16, 2007

Set @AVG = ROUND ((V1*5+V2*5)/10,0)


What is the difference between these codes?

Set @AVG = ROUND ((V1*5+V2*5)/10.0,0)


For values V1=80, V2=85;
First code gives 82! Second gives 83!:confused: :confused: :confused:

View 6 Replies View Related

ROUND Function In Ms-sql Server

Jun 13, 2006

Hi all

if i run
SELECT ROUND(700/1224) -- from sql server queyr analysier iam getting value as "0"

but same query i run in Oracle

SELECT ROUND(700/1224) FROM DUAL; iam getting value ".571895425"

what is reason can some body explan me

View 4 Replies View Related

Round Function (Zero Elimination)

Jul 19, 2007

Hi

I use round function

select round(1234.4545,2)
GO

Result
1234.4500

I want last two zero to be eliminate.
Please advise how?

Thanks
Jawad

View 3 Replies View Related

Round Function In Derived Column Transformation

Oct 31, 2007

Can somebody please help me with the implementation of a logic in round off to the left of a decimal point.
Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help

Value x Excel SSIS
627900 16200 16221
187000 4800 4831
277760 7200 7175
763000 19700 19711
1387500 35800 35844
1465200 37900 37851
2725000 70400 70396
292800 7600 7564
317200 8200 8194

The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.

View 3 Replies View Related

Round Function In Derived Column Transformation

Oct 31, 2007

Can somebody please help me with the implementation of a logic in round off to the left of a decimal point.
Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help

Value x Excel SSIS
627900 16200 16221
187000 4800 4831
277760 7200 7175
763000 19700 19711
1387500 35800 35844
1465200 37900 37851
2725000 70400 70396
292800 7600 7564
317200 8200 8194

The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.

View 3 Replies View Related

Why These Two SQL Statement Return Differently? Using ROUND() Function In Transact-SQL

Sep 19, 2007

Hello, DECLARE @x DECIMAL
SET @x = 65.554
SELECT ROUND(@x, 1)--this returns 66

SELECT ROUND(65.554, 1)--this returns 65.600 can someone explain to me why is like that?
 Thank you
 

View 3 Replies View Related

[OT?] TSQL Function

May 9, 2008

Hi,
I need some pointers, I am trying to create a SQL function which will check to see if a column in a table will allow null values to be inserted.
I've tried searching the net but to no avail.
Kind Regards
 

View 2 Replies View Related

TSQL Function

Jun 20, 2008

Hey guys,
I have been trying to get a function to work but have been having a hard time. The tables that are used are a translation and validation tables. But the translation can be from different tables. What i would like the function to do is take in a 'description of a type' and a language code and send back the translated version. The issue is that the table is dynamic and there is my issue with my function.

ALTER FUNCTION [dbo].[Trans]
(
@Trans_description AS NVarchar(200),
@languageid AS nvarchar(10)
)
RETURNS nvarchar(50)
AS
BEGIN
DECLARE
@return AS nvarchar(50),
@TABLENAME AS nvarchar(50),
@sql AS nvarchar(100)
set @TableName = (select top(1) listtable from validationType where validationtypeDesc = @Trans_description)

SET @return =
(SELECT TOP (1) ValidationDesc FROM [translationvalidationlist] WHERE validationpartyid IN
(SELECT validationpartyid FROM @TableName WHERE validationtypePartyid IN
(select validationtypepartyid FROM validationtype WHERE validationtypeDesc = @Trans_description
))AND LANGUAGEID = @languageid)


RETURN @return



END

Is there anyway to do a @return = exec(@sql) of course sql would be the current @return. Right now i get the error Must declare the table variable "@TableName".


Thank you so much for all your help.

View 4 Replies View Related

TSQL - Using Sum Function

Sep 18, 2007

Hi guys,
I am looking for a solution to the problem described below (SQL SERVER 2000).
Thanks in advance for any help,
Aldo.

The script below:



Code Snippet
SELECT
Data.[Invoice No] AS 'InvoiceNo',
[JurnalTrans.REF2] AS 'JurnalTrans.REF2',
[JurnalTransMoves.SUF] AS 'JurnalTransMoves.SUF',
[RowIDDataLevel] AS 'RowIDDataLevel',

CASE WHEN RowIDDataLevel = 1 THEN CAST(Paid AS int) ELSE '0' END AS 'Paid' ,

CASE WHEN RowIDDataLevel = 1 THEN CAST([JurnalTransMoves.SUF] - [Paid] AS int) ELSE '0' END AS 'OutstandingBalance'

FROM CTE AS Data
INNER JOIN (SELECT [Invoice No], SUM([JurnalTransMoves.SUF]) AS Paid FROM CTE GROUP BY [Invoice No]) AS SUMSUF ON SUMSUF.[Invoice No] = DATA.[Invoice No]

ORDER BY [Accounts.ACCOUNTKEY], Data.[Invoice No], [RowIDDataLevel]

Gives these results:

Invoice No JurnalTrans.REF2 JurnalTransMoves.SUF RowIDDataLevel Paid OutstandingBalance

----------- ---------------- ---------------------- -------------- ----------- ------------------

5752 NULL 1155 1 1810 -655

5752 2032 400 2 0 0
5752 2033 155 3 0 0

5752 2034 100 4 0 0

5754 NULL 1732.5 1 2482 -750

5754 2035 750 2 0 0

16687 NULL 2555.8 1 2555 0


Where:
1810 (Paid) = 1155 + 400 + 155 + 100

2482 = 1732 + 750
and so on...

What I need is to get:
instead of 1810 ==> 655 = 400 + 155 + 100
instead of 2482 ==> 750
and so on...

In order to do that, I tried inserting "WHERE RowIDDataLevel Not In (1) " in the line:


Code Snippet
INNER JOIN (SELECT [Invoice No], SUM([JurnalTransMoves.SUF]) AS Paid FROM CTE WHERE RowIDDataLevel Not In (1) GROUP BY [Invoice No]) AS SUMSUF ON SUMSUF.[Invoice No] = DATA.[Invoice No]

and then getting the following:

InvoiceNo JurnalTrans.REF2 JurnalTransMoves.SUF RowIDDataLevel Paid OutstandingBalance

----------- ---------------- ---------------------- -------------- ----------- ------------------

5752 NULL 1155 1 655 500

5752 2032 400 2 0 0

5752 2033 155 3 0 0

5752 2034 100 4 0 0

5754 NULL 1732.5 1 750 982

5754 2035 750 2 0 0


That it is almost what I need, but not good enough because it makes the line marked in red to disappear because of RowIDDataLevel =1
16687 NULL 2555.8 1 2555 0
to disappear.




View 3 Replies View Related

Greatest Function In TSQL

Aug 20, 2007

Hi All,
I need to find the highest of two numbers and lowest of the two numbers.
I read on the web that Greatest and least functions in sql can help me do that.
but when i use
greatest in my query it gives me this error

GREATEST is not a recognized built-in function name.
all I have is

declare @first int, @second int
set @first='12'
set @second='14'
select GREATEST(@first,@second)

Can somebody point me in the right direction.
Thanks so much

View 8 Replies View Related

TSQL - Using More Than One Argument In LIKE Function

Aug 19, 2007

Hi guys,

I am using the LIKE function combined with a CASE WHEN to change a long list of words, but the list is too long...
Is there any posibility to insert more than one argument into one like function...?
Any other good ideas?
Below an example of the code I am using..

Thanks in advance,
Aldo.




Code Snippet
Case
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument01%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument02%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument03%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument04%' THEN 'Result'
ELSE ''
END AS 'Result'


View 2 Replies View Related

DLookup Function In TSQL

May 24, 2008



Hi all,


Access has a DLookup function that allow you to look up a value in another table based on a criteria, how is this functionality achieved in TSQL?


Thanks


Regards
Melt

View 7 Replies View Related

TSQL Function To Return Numeric Value Of Non Numeric Field

Jul 20, 2006

I need to replace Access Val() functions with similiar function in sql.

i.e. Return 123 from the statement: SELECT functionname(123mls)

Return 4.56 from the satement: SELECT functionname(4.56tonnes)

Any one with ideas please

Thanks

George


View 1 Replies View Related

T-SQL ROUND(decimal, Int) Vs C# Round(Decimal, Int32)

Jan 30, 2006

Anybody noticed that SQL Server rounds up if the value is half waybetween two rounded values, but C#'s Decimal.Round(Decimal,Int32)rounds to nearest even number?[color=blue]>From MSDN: "When d is exactly halfway between two rounded values, the[/color]result is the rounded value that has an even digit in the far rightdecimal position. For example, when rounded to two decimals, the value2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process isknown as rounding toward even, or rounding to nearest."I perform the same calculation sometimes on the web server in C# andsometimes at the database in T-SQL, but want to get the same resultfrom both calculations. Could anybody offer any strategies for dealingwith this?Thanks ~ Matt

View 3 Replies View Related

Equivalent Tsql For Sql Server 2000 Is Needed [from Sql Server 2005 Only Tsql]

Nov 19, 2007

Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005

1
-- Full Table Structure

select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id


2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id

This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result for sql server 2000

View 1 Replies View Related

Round Up

Sep 14, 2006

X is a float and I need to perform x/10 and round the result up to the integer. So if result is 0.4 -> 1, if 1.1 -> 2. How can I do this with SQL? 

View 1 Replies View Related

Round Up And Round Down In Sql

Sep 9, 2007

 I want to do a simple thing but it seems to be behaving not as i am expectingI want to round number either up or down....e.g: 4.3 should round to 4  4.7 should round to 5when i use the round function like this:  83/17=4.88round( 83/17 , 0 ) it gives the answer as 4....when i was expecting it to be 5.... i know there is a ceiling function...but depending on the value of the division sometimes i want it to round up and sometimes round down. how can i do this? hope this makes sense. thanks  

View 3 Replies View Related

Round Bug

Mar 25, 1999

Has anyone been experiencing problems with rounding to 2 decimal places in SQL 7? I have a bunch of queries that generate web reports. Under 6.5 everything was fine. Now under 7.0 any number that needs to be rounded to 2 decimals is actually giving me several decimal places.

Here is a simplified version of what I am doing:
DECLARE @x real
DECLARE @y real
SELECT @x = 223.1
SELECT @y = 59.7

SELECT ROUND((@x/@y),2)

Result should be 3.74. But instead I am getting 3.7400000000000002

If anybody has heard if Microsoft has declared this as a known bug please let me know.

Thanks for your time.

View 1 Replies View Related

Round ?

Aug 22, 2002

I have the statement below which I use in an update

select (round(sum(tottime/60),2)) as ttime from vw_cms_suptime
where vw_cms_suptime.[tracking number] = 970
tracking.[tracking number]

Even though I have the round the statement returns a value
of 5.0000000000000003E-2

Is there something wrong with the round?

View 1 Replies View Related

Round The Value

Apr 17, 2008

Hi,

I need to display the value of a certain varible rounded to two digits after decimal.

For example :

value of a is 1346.8500

I need to get the value of a as 1346.85

Please help me

View 4 Replies View Related

Round To Near Value

Jan 4, 2007

hi,i have one criteria

if if i have time as 1:15 min means it has to show 1:15,if i have 1:20 min it has too be rounded and it has to show 1:30 min.
can any one give me query for this
thanks in anvance

View 3 Replies View Related

Round Off

May 14, 2007

How to round off the value .579 into .6 using round function.i tried doing it but in vain.

View 3 Replies View Related

Round Off

Jul 30, 2007

how do i round off value 0.23 to 0.2.
i am using this inside a scalar function and the return type is numeric(6,2).
so how do i get 0.2 and not 0.23

View 4 Replies View Related

How Do I Round The Value ?

Aug 10, 2007

Hi

I have datacloum called 'price' (float) and using the below code in my stored procedure

cast(price * 1.175 as decimal(19, 2)) as [item-price]

I am getting the price as (ex1: 23.58, ex2: 114.25, ....etc)

So How do I round the value(price) to (ex1: 23.99, ex2: 114.99)

Advance thanks

View 3 Replies View Related

Round Off

Aug 23, 2007

i want to round off 0.23 to 0.25 ,i am trying it with round function but can't get it.

View 3 Replies View Related

How ROUND?

Jul 20, 2005

Hi,i need to round:3° decimal between 1 and 5 LOW3° decimal between 6 and 9 UPThanks

View 1 Replies View Related

Round

Oct 10, 2007



There is a filed in a table of type decimal(12, 8)

when running a select query on this table for this field i.e.
select field1 from table1

the data is shown like:
102.12500000
104.12500000

And therefore the report shows the same figure.

How do I get this field to show up in the report as 4 decimal places. i.e.
102.1250
104.1250

Tried the properties of the cell to format the text into a number but there is only 2 decimal places there.
Even tried the expression by using Rnd(field1). This does not seem to do what I am after.


Thanks

View 1 Replies View Related

Round A Number

Jun 26, 2000

How do I write sql syntax to round the last digit. For example, 12.152 should be 12.15. My current sql syntax does not round the last digit as follows:

Select tblARInvoiceDetail.UnitPrice * tblARInvoiceDetail.Quantity AS ChargeBeforeDiscount
FROM tblARInvoices INNER JOIN
tblARInvoiceDetail ON
tblARInvoices.ARInvoiceID = tblARInvoiceDetail.ARInvoiceID


The tblARInvoiceDetail.UnitPrice column is numeric;length 9;precision 18
The Quantity column is integer;length 4; precision 10;scale 0

Any Suggestions?
Thanks,
Denise

View 1 Replies View Related

Round Problem

May 5, 2004

Hi,

I am using sql statement to save data in SQL SERVER but even i did not apply any round function it is automatically rounding up. e.g. 3.56 when i see it in database it is 4 how can i avoid this rounding? I am using MS Access as front end.

Any help will be highly appreciated.

View 11 Replies View Related

How To Find Max Round

Feb 12, 2014

I've a table having the following fields :

state|dist|company|round
1 | 1 | 2 | 2
1 | 1 | 3 | 3
2 | 1 | 3 | 2
2 | 1 | 1 | 4
2 | 2 | 12 | 1
2 | 2 | 9 | 2
2 | 2 | 4 | 3

I want to extract the max round in the state,district with the adjacent company.

View 3 Replies View Related

Round Off Sum Values

Mar 19, 2014

I have already summed the values and this below query sum the values and returns zero if there are no values. How can I roundoff the values for these columns.

isnull(sum([Old_Values]),0) [Old Values],
[New_Value] = isnull(sum([Yearly_Value]-[Previous_Year_Value]),0)

Even this query I use for calculation and I need to round off the values

[Product_Price] = [Product_Profit]/4,
[Product_Profit] = [ActualValue] * 0.75,
[GrossValue]

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved