/*********** Script 1 **************/
declare @nr_1 as decimal (10,2)
declare @nr_2 as decimal (10,2)
set @nr_1=5
set @nr_2=3
select round(@nr_1/@nr_2,0)
RESULT = 2
/*********** Script 2 **************/
select round(5/3,0)
RESULT = 1
What it is the explication for these difference ?
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 RelatedX 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
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies View Related
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
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.
Hi All,
Is there a way in T-SQL using the round command to always round up regadless of the value.
For example:ROUND(normal_hours * pay_rate * 52, - 3) AS Expr3
normal_hours = 36
pay rate = 23.64
weeks in year = 52
(36 * 23.64) * 52 = 44,254.08
It rounds to 44,000. I want 45,000. Is this possible. Am I overlooking the obvious?
Thanks in Advance and Happy Holidays!!!!!!!!Adam
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:
for MS SQL 2005 I am having
SELECT name.Name
, Percentage = (count(*) / ((SELECT tot = COUNT(*) * 1.0 ) FROM dbo.Name)) * 100)
FROM dbo.Name
GROUP BY Name
how can i CAST and ROUND (count(*) / ((SELECT tot = COUNT(*) * 1.0 ) FROM dbo.Name)) * 100) ?
then ROUND (the result, 2) ?
CAST(count(*) / ((SELECT tot = COUNT(*) * 1.0 ) FROM dbo.Name)) * 100) AS DOUBLE does not work
thank you for helping
I have a column called as NDM$ What I want do it round it the nearest value example I am giving below
34.100->34%
39.8->40
35.4->35 some thing like that.