How To Round With Negative Numbers?

Jul 23, 2005

I am using a select statement to obtain a result set back with aggregated
data. The problem is that I am seeing column data with 11 to 13 digits
after the decimal point. I tried using the STR function, but then the Order
By clause does not sort properly because there are negative numbers in the
aggregated data... I tried using Round, but that does no good either - it
still ends up displaying too many digits after the decimal point. Right now
I'm just using Query Analyzer to display the data, so I can live with it for
now. But, in the future, my app will be getting a result set back and I
would prefer not to have to go through each row and do a round on it from
the program. Does anyone know how to solve this problem?

Thanks for any help,

Bob

View 4 Replies


ADVERTISEMENT

Query Analyzer Shows Negative Numbers As Positive Numbers

Jul 20, 2005

Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

View 7 Replies View Related

Sp_spaceused With Negative Numbers?

Mar 1, 1999

Hello:

I have gotten the following information from sp_spaceused:

----------------------------------------------------------------------
database_name database_size unallocated space
------------------------------ ------------------ ------------------
EP_tran2 700.00 MB 273.21 MB

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
437034 KB 295106 KB 1447796 KB -1305868 KB
--------------------------------------------------------------------

I unfortunately since this is for a test database that I have put the data and log on the same device.

My questions are:

1) Why do I have negative numbers?

2) Should I be concerned about this?

3) for a live database can I drop the log and put the log on another device? What do I need to do?

4) has anybody seen this before?

5)what can I use to get accurate numbers?

THanks. Any information will be greatly appreciated.

DAvid Spaisman

View 1 Replies View Related

Problems Importing Negative Numbers From Excel

Aug 14, 2007

Hi there,

I am trying to import about 3300 lines of data from excel. Some of the columns in excel have negative, positive and even 0 as numbers.

I have matched those columns in the DB and have tried all the different data types that i thought should work, but on import, all negative and 0 numbers get dropped.

I have played with the excel formatting as well, but nothing seems to work.

Anyone encounter this before?
Thanks,

View 14 Replies View Related

Can't Convert Negative Numbers From String Format

Oct 11, 2007

Hello,
I'm having problems with a column of numeric string data coming from a tab delimited CSV file. When a number in the column is negative, it is expressed this way; 1,240.52-

The negative symbol occurs at the end of the numeric string. The destination column is in a SQL Server 2005 table. I've tried to change the data type of the table column, and three or four different data types in a data conversion task, but nothing has worked so far to bring the data over.

Any ideas?

Thank you for your help!

cdun2

View 4 Replies View Related

The Zero Line Seperating Postive And Negative Numbers Should Be Darker

Aug 14, 2007



Hi,
I am having chart which has positive and negative numbers.I need dark line(gridline) at 0.00th location.So that it differentiates between positive and negative levels.So please help me out in solving this asap.

Thanks in Advance
Shri

View 4 Replies View Related

Reporting Services :: SSRS Is Not Subtracting Negative Numbers It Adds Them

Apr 20, 2015

=Sum(Fields!balance_due.Value)
Customer Name Balance Due
1001 Bob 100.99
1002 Jim 10.95
1003 Kim -12.67
1004 Lisa 13.23
Total $137.84

It should be like this:

Customer Name Balance Due
1001 Bob 100.99
1002 Jim 10.95
1003 Kim -12.67
1004 Lisa 13.23
Total 112.5

How can I get SSRS to add and subtracted numbers.

View 10 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?



View 1 Replies View Related

I Need To Update A Table With Random Numbers Or Sequential Numbers

Mar 11, 2008



I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.

Thanks for any help

View 6 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

Generate List Of All Numbers (numbers Not In Use)

Feb 21, 2007

I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:

select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)


but don't know how. Any clues?

View 1 Replies View Related

Dataflow To Excel - Convert Numbers Stored As Text To Numbers Excel Cell Error

Mar 27, 2007

I'm trying to write data to excel from an ssis component to a excel destination.

Even thought I'm writing numerics, every cell gets this error with a green tag:

Convert numbers stored as text to numbers

Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.

I'm hearing this a common problem -

On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:

<style>.text { mso-number-format:@; } </style>

is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.

Maybe some setting in my write drivers - using sql servers excel destination.


So close.. Thanks for any help or information.

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

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

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

Problems With Round()

Feb 19, 2008

I understand that when you use ROUND(8.5,1), I would get 9 but no matter how hard I try, I get 8.

I had this code that suppose to get the round of a quotient. I wanted to update a number of records by converting them via multiplication and/or division.

Here:

UPDATE Length
SET inchTOcm = inchTOcm*2.5, metricTOton = ROUND(metricTOton/1.1,1);

supposing the original value of inchTOcm are in inch and in metricTOton are in metric ton.

There is a value in the metricTOton that when I divide it with 1.1, it's 345.81818. However, when I round it, it displays 345 instead of 346.

Is there wrong? How can I do this without using the CASE statement?

View 3 Replies View Related

Get Round Of Getdate()

Mar 1, 2008

Hi,
i write a query

select getdate()

suppose output come '2008-03-01 14:08:52.187'

i want to get like this output

'2008-03-01 14:09:00'

means want to neglate second and milisecond part and want round of getdate()



Ranjeet Kumar Singh

View 1 Replies View Related

Round() Problem

May 20, 2008

Hi,

When I use the following query
select round(7.35,1)
the output is 7.4.

But when I use this query

declare @tot float

set @tot = 7.35

select round(@tot,1)

the output is 7.3

How do I make the output consistent here desirable is 7.4. Please advise.

View 4 Replies View Related

Can't Seem To Round An Average.

Feb 8, 2007

I am using an expression to create an average. The result is an odd number like 33.666666666666667.

I want to round this to 34 (I would even accept 33, or 33.67), just not so manny digits to the right.

View 1 Replies View Related







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