Has any of you ever had problems with the ROUND command?
I have this operation:
ROUND((FIELD1 / 360 * FIELD5),3) AS FIELD7
(I want the result to be rounded at the third decimal).
SQL makes mistakes in rounding. It seems to me that
the problem relies in the quantity of decimals taken
into account in any single step of the operation.
Thank you in advance.
Anna - Verona (Italy)
The query below gets percentage of coils for a location by anneal cycle. It does this correctly (ie, if you change count(*)/b.total to count(*), b.total ... show both columns, the numbers its pulling are correct), but in its current state (trying to divide count by total), it always returns a zero (no decimal values).
If I change that section to : (count(*)*100/ b.total*100)/100, I get integer percentages: no decimals (this is a problem because some records round off to zero). I'm pretty sure this is the entire problem (why I'm getting zeros as written below), but I don't have a clue how to fix it. Is this some kind of server setting that allows the query to only return decimal values or something?
select a.department, a.cycle, count(*)/ b.total percentage
from inventory a join (select department, count(*) total from inventory
where location not like 'bas%' and archived = 0 and quality_code = 'p'
group by department) b on a.department = b.department
where a.archived = 0 and a.quality_code = 'p' and location not like 'bas%'
group by a.department, a.cycle, b.total
order by a.department, a.cycle
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 RelatedI want to get the list of names starting with a particular letter.this letter should be passed as parameter in the query.
I have used the following query,
Select Cust_Code,Cust_Name,Cust_Add1 + Cust_Add2 as Address From Customer Where Cust_Code Like @Cust_Code Order By Cust_Name ASC
I am not sure whether the above query is correct.
Hi all,
I have this store procedure as follows:
Create Proc UpdateProblem@ProblemID int,@CompanyName varchar (50),@Firstname varchar (50),@Lastname Varchar (50),@Address varchar (50),@Postcode varchar (50),@City varchar (50),@Phone varchar (50),@Cutype varchar (50),@ProDescript varchar (50),@Sol varchar (50),@Email varchar (50)
as Update Problemset CompanyName = @CompanyName,Firstname = @Firstname,Lastname = @Lastname,Address = @Address,PostCode = @Postcode,City = @City,Phone = @Phone,Cutype = @Cutype,ProDescript = @ProDescript,Sol = @Sol,Email = @Email
where ProblemID = @ProblemID
when I test the querry
exec UpdateProblem10004, 'Toro AS','Mike','Tullas','Togo Street','G34 5TT','New York','06582531','Private','Machine is dead','Replace motherboard','goo@ht.com'
what happen is that when I ran the querry instead of updating the specifc row of 1004 the querry will just update the whole rows in the table with the same data.
Please help. I have set the ProblemID as the Primary key.
I often run queries in Query analyzer. Today I accidentaly deleted some data from a table. Is their a way to undo the last SQL command executed?I have heard of the rollback command, but I don't know if that is applicable or not?
View 6 Replies View Relatedhow to get the quantity from output of sql query command for example, I could get the container name by below command
select container_name from sysibmadm.snapcontainer
get the container number by
select TBSP_NUM_CONTAINERS from SYSIBMADM.SNAPTBSP_PART
now I want to get the container number by below command output result select container_name from sysibmadm.snapcontainer..so what more command should I add on above command, I mean I want to get the container number by container name from the output of above command, not by 'join'.
Hi all,
I have a script to bulk copy data from my table into textfile. This bcp script will be executed by xp_cmdshell. When I try to execute the scipt inside query windows it returned an error : Error = [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. But when I test the bcp script in command prompt it will execute successfully
Is there any misconfiguration ? (I'm using sqlexpress) thanks in advance.
Best regards,
Hery
sorry if when i had one table result like this ,
select changeprice,usetype from orderchange
changeprice usetype
-------------- ----------
450 a
650 b
650 c
how the sql query command i can use to be select result like this in one column line.
450a 650b 650c
best regard
I was wondering if there was a command in SQL that you could enter that would stop the execution of a query at a specified spot.
For instance if you were debugging what a batch of update queries were doing to a table and you wanted to stop it at a specific point. I realize you can select the code you want to run, but I would prefer to enter something at a point that stays whether I am selecting it or not. That way when I am looking at a 1000 lines of code I don't have to remember everytime how much to select .
So are there any commands like that? I was thinking something like 'Stop' in VBA but that doesn't work
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 RelatedI 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 RelatedHas 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.
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?
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
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
How to round off the value .579 into .6 using round function.i tried doing it but in vain.
View 3 Replies View Relatedhow 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
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
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 RelatedHi,i need to round:3° decimal between 1 and 5 LOW3° decimal between 6 and 9 UPThanks
View 1 Replies View Related
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
Dear Friends,
I have an OLE DB Command inside an ETL. I want to execute a stored procedure that returns 2 fields, for example:
CREATE PROCEDURE sp_Name
AS
SELECT Field1, Field2 FROM Table
How can I continue the dataflow? How can I return the values from the query? Returns only a row...
Do you know of a way, in the OLE DB Command (Executing a sql command for each record), to specify a lock hint?
Example: in my component, I want to update a table but I want to specify a ROWLOCK or UPDLOCK hint.
When I try using this sql statement in the 'SqlCommand' property:
UPDATE DIM_Accounts WITH (UPDLOCK)
SET COL1 = ''
SSIS gives a syntax error.
Do you know any workarounds?
Thanks!
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
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.
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
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.
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]
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?
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
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.
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.