SQL Server 2008 :: Incorrect Rounding With Decimal?

Oct 17, 2015

My code is rounding my values incorrectly and I'm not sure why. In this example, the numerator is 48 and the denominator is 49 which is .9795 but my SQL is producing 97.0. I would like to result to be 97.9

CONVERT(decimal(4,1), (SUM(CASE Score_CorrectID WHEN 1 THEN 1 ELSE 0 END +
CASE Score_MiniMiranda WHEN 1 THEN 1 ELSE 0 END +
CASE Score_RepAssistance WHEN 1 THEN 1 ELSE 0 END+
CASE Score_Tone WHEN 1 THEN 1 ELSE 0 END +
CASE Score_Consol_Default WHEN 'OK' THEN 1 ELSE 0 END) * 100)
/
SUM(CASE WHEN Score_CorrectID IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_MiniMiranda IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_RepAssistance IS NULL THEN 0 ELSE 1 END+
CASE WHEN Score_Tone IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_Consol_Default IS NULL THEN 0 ELSE 1 END)) AS Avg_Percent_Actions

View 3 Replies


ADVERTISEMENT

SQL Server 2012 :: Rounding With A Decimal

Sep 3, 2014

I would like the

DECLARE @MyPay decimal(5,9);
SET @MyPay = 258.235543210;
SELECT CAST(@MyPay AS decimal(5,2))

This is what the resultset is currently with the code above:

258.24

I would like to Not have the value round up. I would like to always show only the first two digits to the right of the decimal and not perform any rounding.

View 5 Replies View Related

SqlDbType.Decimal Rounding Up

Nov 15, 2007

I have a field in my SQL Server 2005 database of type   numeric(18,3)In code, I treat the value as decimalWhen creating my command parameters, this is how I'm declaring them:prm.SqlDbType = SqlDbType.Decimal;prm.Precision = (byte)int.Parse("18");prm.Size = int.Parse("0");prm.Scale = (byte)int.Parse("3");Inserting a number like  5.687 is rounding to 6.000            anyone know why it is doing that?

View 5 Replies View Related

Decimal Rounding To 2 Digits

Feb 16, 2012

This give me 6 digits to the right of the decimal point - can round it to 2???

CONVERT (decimal(18 , 2), 1.0 * NULLIF (vVotesR, 0) / NULLIF (vVotesR + vVotesD, 0) * 100)

View 3 Replies View Related

SQL Server 2008 :: Rounding To Nearest Quarter Hour?

Aug 12, 2015

I am getting the time difference between two dates using

DATEDIFF(second,Information.[Start Time],Information.[End Time]) / 60.00 / 60.00 AS hours,

My output looks like
1.33
0.17
1.50
etc

I'd like to round to the nearest quarter hour

1.50
0.25
.150
etc

View 4 Replies View Related

Decimal Field Is Rounding Up My Numbers

Feb 4, 2006

Hi,
I have a decimal field in SQL Server 2000 which has a precision value of 3 and scale 1. I will be storing values ranging from 0.5 to 10.0 in there. However, in my asp.net web form, if I select the value 2.5 from the DropDownList, SQL Server stores it as 3.
Can anyone tell me why this is happening and give me some pointers on what I can do to fix it? Your help is much appreciated.

View 9 Replies View Related

Fixed Decimal Places Without Rounding

Mar 1, 2001

What is the best way to force a 2 digit decimal place without rounding?
For example select price*UOM returns
47.1294
3.255
.5
8
.49

What i want to be returned is
47.12
3.25
8.00
.50
.49

Thanks,

Jim

View 3 Replies View Related

Convert Decimal To String Is Without Rounding Up

Jul 27, 2004

Hello I'm trying to write a SQL Statement along the lines of....

SELECT stringField + ' : ' + STR(decimalField) AS myField FROM tablename WHERE myCondition = myValue

Where stringField is a String field and decimalField is a Decimal Field in my Table.
In this statement it converts the decimal field to a string value so that it doesn't throw a conversion error but unfortunatly it seems to round up the value to an integer value and cuts off all my decimal places.

How can I get it to keep the Decimal Places?

View 5 Replies View Related

Varchar To Decimal Rounding Issues

Jul 9, 2007

hi all

I have a problem with SQL rounding my decimals up when I pull them from a temp table that has the column set as VARCHAR. What is happening is I am pulling the info from a flat file but each column has "" around each field so I must make the temp table columns all VARCHAR so I may pull the info from the file properly. So after the info has been extracted, I run an update statement on the temp table to remove all quotes. Once this is done, the revised info is inserted into a staging table and any field that is a represented as a decimal is labled as such in the staging table.

What I am running into is when the info is inserted into the staging table, the decimals are rounded up to whole values. The column has been checked to verify that it is indeed a decimal. I even have a CAST statement in the insert hoping to combat the rounding issue, but it is not helping.

So what reason(s) would my decimals be rounding up?

Thanks alot

View 3 Replies View Related

Rounding Decimal Points In An Expression

Nov 1, 2006

This sounds so simple but yet don't know why it doesn't work.

i've got two decimal numbers in columns

closingUnits = 25093.53640

closingAmt = 59110.33

i use a derived column transformation to generate a string column which is the division of those two above.

mystrfield = closingAmt /closingUnits = 2.355599

what i want is cast it to just 4 decimal points when i run the expression below it cast it to 4 decimal points but it doesn't do any rounding.

the value i get is 2.3555 when i should get 2.3556

(ISNULL(closingAmt ) || closingUnits == 0) ? "0.0000" : ((closingAmt / closingUnits) < 1 && (closingAmt / closingUnits) > -1 ? "0" : "") + (DT_WSTR,15)(DT_DECIMAL,4)(closingAmt / closingUnits)

View 1 Replies View Related

How To Remove Decimal Point From Number Without Rounding

Aug 30, 2013

How do you display 12.38 as 1238 I can't use round.

View 2 Replies View Related

T-SQL (SS2K8) :: Rounding Decimal To Nearest Integer Value?

Jun 28, 2015

how to round the nearest value after round of decimal and return integer.

declare @data decimal(18,2)
set @data = 5.55

-- output

select '5'
set @data = 5.58
-- output
select '6'

View 3 Replies View Related

Rounding On Decimal Data Type (noob Question)

Feb 22, 2006

Hi all,

I'm trying to update a decimal field with a single decimal number (1.8) the problem i'm having, is that it's rounding the number up (2). I would've thought that a decimal datatype would keep the decimal places correct?

This is quite annoying.. I've been searching around the web for an answer.. To no avail (I'm probably asking the wrong question)

Can someone please point me in the right direction?

View 5 Replies View Related

Rounding To 2 Decimal Places In Derived Column Transform Editor

May 15, 2008

I want to replace the contents of a value column with itself but rounded to 2 decimal places.

The current column is a double and I have tried to perform this using the following expression but it fails to work.






Code Snippet

Round(cc_vl,2)
How should I achieve this?

View 7 Replies View Related

SQL Server 2008 :: Using Decimal Columns In SUM Function

Jul 21, 2015

Recently I have come across a requirement where i need to design a table.

There are some columns in table like below with DECIMAL Datatype:

BldgLength

BldgHeight

BldgWeight

Based on my knowledge, i know that values before Floating-Point will not be more than 4 digits.

Now as per MSDN,

Precision => 1 - 9
Storage bytes => 5

so i can create column as:

BldgLengthDECIMAL(6,2) DEFAULT 0

OR

BldgLengthDECIMAL(9,2) DEFAULT 0

Now while reading some articles, i came to know that when we do some kind of operation like SUM Or Avg, on above column then result might be larger than current data type.

So some folks suggested me that i should keep some extra space/digits considering above MATH functions, to avoid an Arithmetic Over Flow error.

So my question is what should be value of DataType for above column ?

View 8 Replies View Related

SQL Server 2008 :: Limiting To 2 Decimal Points

Aug 31, 2015

The total quantity(sum(woitem.qtytarget) as total) result set is showing more than 6 numbers after the decimal point (example:442.2565485). How can I limit it to 2. (example:442.25)

select wo.num, sysuser.username,sum(woitem.qtytarget) as total
from woitem
join wo
ON wo.id = woitem.woid
Join moitem
on moitem.id = woitem.moitemid
Join mo
ON mo.id = moitem.moid
LEFT JOIN SYSUSER ON mo.userid = sysuser.id
group by sysuser.username, num
ORDER BY CAST( REPLACE( wo.num, ':', '.') as decimal(12, 3))

View 1 Replies View Related

SQL Server 2008 :: Difference Between Money And (Float Or Decimal) Datatype

Jan 16, 2013

What is the difference between Money and (Float or Decimal) Datatype. If we use Float or Decimal instead of Money, will we loose any functions..?

View 4 Replies View Related

SQL Server 2008 :: Dynamic Script Error - Incorrect Syntax Near Keyword FOR

Apr 17, 2015

declare @sql nvarchar(MAX)
SELECT @sql = (SELECT 'UPDATE STATISTICS ' +
quotename(s.name) + '.' + quotename(o.name) +
' WITH FULLSCAN; ' AS [text()]
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type = 'U'
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
PRINT @sql
EXEC (@sql)

The below Dynamic TSQL throws Error:

Error:
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'FOR'.

USE master

GO
DECLARE @str varchar(max), @sql nvarchar(MAX), @dbName nvarchar(max);

SET @dbName = 'user_db';

PRINT N'CHECKING DATABASE ' + @dbName;
SET @sql = 'USE ' + @dbname + ';' + '(SELECT '+'''UPDATE STATISTICS ''' + '+ ' + 'quotename(s.name)'+ '+' + '''.''' + '+' + 'quotename(o.name)' + '+' + '''WITH FULLSCAN; ''' + ' AS [text()]
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type ' +'= ' + '''U'' FOR XML PATH('' ''),TYPE).value(''.'''+ ','+ '''nvarchar(MAX)'''+')'
Print @sql
EXEC (@sql)

Not sure, why this is an error at FOR...

View 7 Replies View Related

SQL Server 2008 :: Incorrect Prefix Error (select Count Statement)

Oct 7, 2015

Naming convention and what am I doing wrong here:

,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where Channels.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and c2.DateContractFunded < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedLastYear
FROM Channels AS C
INNER JOIN ChannelContacts AS CC ON C.ChannelID = CC.ChannelID
INNER JOIN ChannelProductPlan AS CPP ON C.ChannelID = CPP.ChannelID
INNER JOIN tblLuMktReps AS MR ON C.MarketRepID = MR.MarketRepID
INNER JOIN tblLuHoldingCo AS HC ON C.HoldingCoID = HC.HoldingCoIDError message:

Msg 107, Level 16, State 3, Line 1
The column prefix 'Channels' does not match with a table name or alias name used in the query.

View 9 Replies View Related

VB Web 2008, Incorrect Syntax Near 'nvarchar'

Jun 18, 2008

I"ve had some issues in developing the sql server portion of my site.  The issue is editing, deleting, inserting data from a form (at least from what I can understand, I'm a beginner).  Below is the error.  Any help I can get is greatly appreciated!
Josh 
 
Server Error in '/WebSite4' Application.


Incorrect syntax near 'nvarchar'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'nvarchar'.Source Error:



An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:



[SqlException (0x80131904): Incorrect syntax near 'nvarchar'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +149
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +404
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +447
System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +72
System.Web.UI.WebControls.FormView.HandleInsert(String commandArg, Boolean causesValidation) +388
System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +602
System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +132
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +177
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746

View 4 Replies View Related

SQL Server Rounding Question

May 28, 2004

SQL Server seems to round this down:

AVG(DATEDIFF(d, startdate, enddate))


I've tried using ROUND(AVG(DATEDIFF(d, startdate, enddate)), 0) but it still rounds down.

For example, if the result is 1370.758, I want it to round to 1371.

Am I missing something?

Thanks.

View 1 Replies View Related

SQL Server 2012 :: Rounding UP Values In Column

Nov 28, 2014

I need to round UP values but they should never be rounded down, below is my expected output in RoundVal column.

SELECT 89 AS Val, 100 AS RoundVal UNION ALL
SELECT 329, 1000 UNION ALL
SELECT 6329, 10000 UNION ALL
SELECT 43299, 100000 UNION ALL
SELECT 155329, 1000000

View 1 Replies View Related

SQL Server 2012 :: Differences In Rounding / Converting Strings?

Dec 9, 2013

difference between

SELECT ROUND ('6.465',2) --- result 6.46
and
SELECT ROUND (6.465,2) --- result 6.47
with

It's because you're relying on an implicit conversion from a string to a decimal data type which SQL server will do to 2 decimal places by default...

Alright:

SELECT ROUND (CONVERT(DECIMAL(3,2),'6.465'),2) --- result 6.47 Now please explain this:
SELECT ROUND('0.285',2) -- 0.28
SELECT ROUND(0.285,2) -- 0.29
SELECT ROUND (CONVERT(DECIMAL(3,2),'0.285'),2) --- result 0.29 The string value does not seem to be converted to decimal with 2 decimal places.

MS is on the safe side with mentioning the last digit is always an estimate But because the result of the estimate is always the same, I would like to know:

* how is a string value exactly implicitly converted?

* how exactly does the estimation work, that in case of doubt rounds a value up or off?

View 2 Replies View Related

Legacy Database Uses Decimal Data Types.--&> AutomobileTypeId (PK, Decimal(10,0), Not Null) Why Not Integers Instead ?

Sep 26, 2007

I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
 
Example: AutomobileTypeId (PK, decimal(10,0), not null)

View 5 Replies View Related

Data Type With Decimal Point For Decimal Values But Not For Whole Integers

Dec 8, 2013

I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?

View 2 Replies View Related

Cast Or Convert Nvarchar With Comma As Decimal Separator To Decimal

Apr 29, 2008

Hello.

My database stores the decimals in Spanish format; "," (comma) as decimal separator.

I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.


Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:

Error converting data type varchar to numeric



Any knows how to resolve.

Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.

View 5 Replies View Related

Converting (casting) From Decimal(24,4) To Decimal(21,4) Data Type Problem

Jul 24, 2006

Hello!



I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?

Thanks for giving me any advice,

Ziga

View 6 Replies View Related

How Can I Use The Decimal Comma Instead Of Decimal Point For Numbers In Jet Engine?

Sep 19, 2007



I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl

View 4 Replies View Related

Converting Decimal To String W/O Decimal Point

Jul 23, 2005

I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?

View 6 Replies View Related

Converting Decimal Point To Decimal Comma

Nov 30, 2007

Hi all,

I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.

e.g.
‚¬1,500,123.00 to ‚¬1.500.123,00

Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.

Any help would be much appreciated

Thanks!

View 5 Replies View Related

Can I Run SQL Server 2008 - Reporting Services CTP Until Microsoft Sells SQL 2008

Mar 31, 2008

There are a few features in the new SQL Server - Reporting Services that I really need in production. I have tested everything and it works great. I am running the CTP version since Microsoft is saying they aren't releasing the release version until 3rd quarter 2008.


Since Microsoft won't sell SQL 2008 until 3rd quarter, can I run the CTP in production until the release and then purchase SQL 2008?


Jim

View 1 Replies View Related

Seeking Advice: SQL Server On Win 2008 Virtual Server Or Just Win 2008?

Apr 23, 2008



Hello - does anyone have experience w/SQL Server 2005 in a virtual environment? I'm considering this for a production environment but not sure if performance will suffer. Our databases will have a lot of writing but not too much reading. A SSRS solution is currently the only app. connecting to the SQL db. Max users to server at any given time will be very low (~10 users max). But the databases are pulling in data from other, outside multiple data sources on a daily basis.

Any pointers to documentation or any advice?

Thanks,

A Brown

View 1 Replies View Related

Decimal Limited To 4 Digits To Right Of Decimal?

Jun 18, 2007





I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.

I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.



It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).

Is the decimal limited to scale 4 ???



Thank you for your help!

Anna





View 3 Replies View Related







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