Subtraction Of Different Precision Values
Sep 24, 2007
We have a field which is decimal (9,2) and another which is decimal
(9,3). Is there anyway to subtract the two and get a precision 3
value without changing the first field to 9,3?
For instance, retail value is 9,2, but our costs are at 9,3 due to
being averaged. To calculate margin (retail-cost), we want that also
to be 9,3, but a basic subtraction comes out 9,2. You can see we
don't want to increase retail to be 9,3 (that would look funny), and
it seems wasteful to store retail twice (one 9,2 for users and one 9,3
for margin calc)...is there any other way?
View 2 Replies
ADVERTISEMENT
May 31, 2015
I've;
Id.........|......type....|.....Value
2001................1...............20
2001................2...............32
2002................1...............19
2002................2...............21
2003................1............... 3
2003................2...............30
I want;
Id........|.......Value
2001.................12
2002..................2
2003.................27
View 7 Replies
View Related
Jul 19, 2004
I am using SQL 2000 in a kind of electronic wallet way. Users out money onto an account and spend it on various services on a system. The cost of those services is deducted from the value in their wallet, and everybody's happy. However, some very strange things have been happening to my transactions; seemingly at random.
Some transactions (such as purchasing time on the Internet) are returning values such as 0.10000000000000001 instead of 0.1. This minute difference affects the user's wallet balance because the rouge digit is subtracted from their account. So instead of a balance of, say, 3.4 they have 3.39999999999999999.
"So what?", I hear you say. Well the problem comes when it's time to give them a refund. They walk over to a kiosk and the machine tells them they have 3.40 remaining in their account (it's nicely rounding up the value), but when they click Refund, it tells them they have insufficient funds to complete the refund! (Note: The refund amount is being compared with the wallet balance). If I go into the database via Query Analyzer it tells me their balance is 3.3999999etc, but in Enterprise Manager the value is 3.4. If try to manipulate the data in any way it is treated as 3.4. However, if I add 0.000000000000001 then QA reads the value as 3.4 and the customer can get their refund.
My questions is this. One, how the hell do I stop this from happening? I only need the two decimal places. Taking the value in a query and round it up/chopping off the remaining decimal points hasn't worked. It always picks up the value as 3.4 in a query. Two, why on Earth is this happening??? Has anyone experienced this problem before.
Thanks in advance to anyone that's read this far down.
View 3 Replies
View Related
Feb 19, 2015
I'm very new to SQL, but I'm struggling with using a math operator in a query,.Imagine a list of prices of goods for two stores. Both stores sell some of the same goods and some goods that they are the exclusive seller, but I want to see where store 2 is undercutting store 1 by exactly three dollars on the same product. I've tried this:
selcct i.store,store,i.product,product,i.price,price from Price_table
where i.store = 1
and exists (select i.store,store,i.product,product,i.price,price from Price_table
where store = 2
and product = i.product
and price = i.price - 3
)
Can I use the subraction operator to pull those prices?
View 2 Replies
View Related
Sep 4, 2013
I need to do SUM on a column values and then subtract it from the SUM of values on a column from another table. I am using SQL server Management Studio, but getting the errors on the following Query
SELECT note_id, cap_int_amt)- SUM (ttl_cap_int_amt) as SUM1,
sum(orig_fee_amt)-sum(ttl_qualfyng_fee_amt) as SUM2
FROM [spstrd00_starrpt].[dbo].[rpt23t]
where [rpt20t].note_id =[rpt23t].note_id
ERROR Message
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.rpt23t'.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "rpt20t.note_id" could not be bound.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'ttl_qualfyng_fee_amt'.
View 4 Replies
View Related
Dec 28, 2007
I have a proc I have written that contains a field called calc_date the field is datetime format
how do I get the days from todays date to calc_date, i.e if calc_date = 12/01/07 and todays is 12/25/07 I want a result that is 24
View 1 Replies
View Related
Jan 9, 2006
Hi, I have a database field of type "money". But when I rerieve it to text box, it shows 5.0000, but I want only 5.00 to be shown. How do I format that?
Any reply will be much appreciated. :)
View 2 Replies
View Related
Feb 20, 2000
Hi!
I'm quite new to SQL Server. I need to set a float datatype to display something like 3.55. However, all values that are stored in the float column are truncated to 4 or some other single digit. How can this be prevented?
Regards,
Sam
View 1 Replies
View Related
Sep 5, 2002
Hello everyone,
I am sure this is a newbie question as I am new to Microsoft SQL server but any help is greatly appreciated. I am populating a SQL database from an AS400. The decimal numbers from the AS400 are coming accross with extra decimals. (ie. 63.02 is coming accross as 63.0200001)
Is there a way to limit the number of decimals in a float or real field - or a SQL command I can put in a script to truncate each field to 2 decimal places after they are populated.
Thanks,
Randy
View 1 Replies
View Related
Nov 8, 2004
Hello,
I have been trying to develop an automatic way of programmatically accessing datasources and performing some predefined(-supported) processing on them.
The question I would like to ask you people has to do with numeric fields. What exactly is precision? Is it the maximum length in digits of a field, or is there more to it? What about a "field's scale", what is it and how does it affect a field's value handling?
Thank you very much,
Dave
View 1 Replies
View Related
Sep 21, 2006
I have a table with a 'quantity' column (decimal 9:3) and a 'price' column (9:3). I have a third column 'amount' with a formula of (price * quantity). The formula gives the correct answer, but the precision is automatically set to 5. Is there any way to set the precision of the result to 2?
Thanks.
View 4 Replies
View Related
Jan 21, 2008
I am using ASP and SQL 2005 Express.I am inserting a timestamp from an ASP page using <%=now%into asmalldatetime field. All of my timestamps are appearing without anyseconds (e.g., 1/21/2008 4:02:00 PM or 1/18/2008 11:32:00 AM).When I view the source for my page is shows the date/time as 1/21/20084:27:31 PM, but for some reason the seconds will be converted to1/21/2008 4:27:00 PMHow do i get more a more precise timestamp?Please help.
View 1 Replies
View Related
Apr 18, 2007
Hi All
I am trying to pull data from Oracle to SQL Server but if I use Oledb Source than I get this error
Error at Data Flow Task [DTS.Pipeline]: The "output column "CUST_ID" (590)" has a precision that is not valid. The precision must be between 1 and 38.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)
The only solution I found is use DataReader Source,
But if I use DataReader Source everything works fine , I mean I am able to see the records and convert it desired data type (using Data Convertion component).
My question is what component should I use as Destination, coz if I use OLEDB Desination I get a red cross on the components although I can map all the columns.....
View 9 Replies
View Related
Nov 3, 2003
Hi I am in the process of creating a new db in sql. In my users table I wish to set the UserIds as Integer datatype. It defualts on precision 4. Does this mean that when the column auto increments as its my primary key with a seed of one, my highest number allowed in the table would be row 9999. ???
Also if you where to store a phone number in your db, what column type would you give it. I have used varChar but its all numbers i want to store. Would this suffice.
Thanks
View 1 Replies
View Related
Dec 7, 2007
Hi , i am receiving data froma flat file it is
These are amount fields
123456.89
i am selecting numeric (8,2) as datatype is this valid please let me know.
View 3 Replies
View Related
Oct 8, 2006
there is a column which type is float in a table, i want to set the precision of its value, for example if its value is 10.333888, i want to get its value as 10.33, how to complete it in a select Sql?
thks
View 3 Replies
View Related
Jan 31, 2008
I receive patient demographic files from hospitals that are in several different formats. I have written translations for each format. I need to upload the files into our accounting software. I have the file layout to upload data and it looks like this.
From To Length
Record Type Code 1 2 2A
Account Number 3 17 15A
Guarantor Name 18 47 30A
Guarantor Zip 125 129 5N
Guarantor Area Code 134 136 3N
In SQL Server I have not found a way to set precision on an int. I have to have the correct length, and data type (A for alfa which is left justified and N for numeric which is right justified) field for a succsessfull upload. Suggestions on what data types to use would be very helpful, and then suggestions on how to output the data in a text file described example above would be a life saver.
View 1 Replies
View Related
May 5, 2008
Hi,
I'm importing data from and oracle database to an SQL one through a SSIS package, I'm getting this error:
"The output column "earned_hours" has a precision that is not valid. The precision must be between 1 and 38".
the package runs but returns this column as NULL values
earned_hours is of type "NUMBER" in oracle (some of the values are decimals), I tried making it numeric(x,y),float or decimal(x,y), but I'm still getting the same results.
does anybody know why is this happening or have a solution for this error?
Thanks
View 5 Replies
View Related
Jun 4, 2008
am getting some unexpected behaviour using datetimes (on SP2, though i haven't tried other builds), as below...
<code>
----------------------------------------------------------------------
---if i run...
select dateadd(day,datediff(day,0,getutcdate()),0) as today
,dateadd(ms,-1,dateadd(day,datediff(day,0,getutcdate()),1)) as end_of_today
/*
i'd expect the result to be...
today end_of_today
----------------------- -----------------------
2008-06-04 00:00:00.000 2008-06-04 23:59:59.999
what i actually get is...
today end_of_today
----------------------- -----------------------
2008-06-04 00:00:00.000 2008-06-05 00:00:00.000
*/
------------------------------------------------------------------------
--i run...
select dateadd(day,datediff(day,0,getutcdate()),0) as today
,dateadd(ms,-2,dateadd(day,datediff(day,0,getutcdate()),1)) as end_of_today
/*
i expect...
today end_of_today
----------------------- -----------------------
2008-06-04 00:00:00.000 2008-06-04 23:59:59.998
i get...
today end_of_today
----------------------- -----------------------
2008-06-04 00:00:00.000 2008-06-04 23:59:59.997
*/
------------------------------------------------------------------------
--even as simple as this, the result is the same...
select cast('23:59:59.999' as datetime) as end_of_day
/*
results in....
end_of_day
-----------------------
1900-01-02 00:00:00.000
*/
</code>
can anyone shed any light? and also suggest how to reliably create a datetime like yyyymmdd hh:mi:59.999 ?
Em
View 6 Replies
View Related
Mar 26, 2008
Hi all,
I'm running a transformation script that's taking decimal(18,10) data and trying to shoehorn it into a numeric(9,6). generally this works, as most of the data in the original table is not using anywhere near the precision it's capable of, but once in a while I run into one that does use it.
Is there any way to automagically reduce the precision so that i can cram the data into the destination table?
___________________________
Geek At Large
View 3 Replies
View Related
Dec 8, 2006
This one cost me a solid half hour yesterday. I'm wondering why onearth the default precision for a decimal type is 18,0. Maybe I'mmistaken. A decimal datatype sort of implies that you'd want somethingafter the decimal!Question is, can I set this database-wide? Like all new decimaldatatypes have a precision of 12,6 or something like that? I haven'tseen anything about this in the googling I have done...
View 3 Replies
View Related
Oct 9, 2007
I have a UserDefinedType that is Decimal(21,6)
The fields fltValorPendente e fltTotal are of this type. Field intSinal is an Integer.
I execute the following query:
SELECT
(intSinal * fltValorPendente) / fltTotal as Coef,
cast((intSinal * fltValorPendente) as decimal(21,6)) / fltTotal as CoefCast
into tmp
FROM tbl
Where fltTotal<>0
As a result, table tmp is created with the following structure:
CREATE TABLE [dbo].[tmp](
[Coef] [decimal](38, 6) NULL,
[CoefCast] [decimal](38, 17) NULL
) ON [PRIMARY]
How come both fields don't get the same precision?
View 2 Replies
View Related
Dec 19, 2006
How do I set Precision and Scale in a calulated column?
I'm trying to limit the decimal points returned in a calculated column but can't find where to set the scale. What am I missing please?
Thanks,
Scott
View 6 Replies
View Related
Nov 2, 2007
Hi, first than nothing the query, I'm on sqlserver 2000:
--------------------------------------------------------------------------------------------------------
declare @ordered float
declare @convertion float
set @ordered = 49.0
set @convertion = 24.0
SELECT
CAST((@ordered / @convertion) AS float) as colA,
CAST((@ordered / @convertion) AS INT) as colB,
cast(CAST((@ordered / @convertion) AS float) - CAST((@ordered / @convertion) AS INT) as float) as [colA - colB],
ABS(CAST((@ordered / @convertion) AS float) - CAST((@ordered / @convertion) AS INT)) * @convertion as [The reminder]
--------------------------------------------------------------------------------------------------------
If you run that you'll get this result:
colA colB colA - colB The reminder
2.04166666666667 2 0.0416666666666665 0.999999999999996
You'll notice the workaround to get the decimal part of a number, if you wonder why its because sql2000 in the operand % only supports integers. Anyway, try on your calculator ( 2.04166666666667 - 2 ) it probably answer: 0.04166666666667.
BUT SQL NOT! you see how the result is amazingly 0.0416666666666665 ????
Does anybody knows a solution for this??? What am I doing wrong???
If you try the query but instead of 49.0 use 25.0, it returns the correct value !!.
I really appreciate any comment about this.
View 1 Replies
View Related
Dec 1, 2004
We have a view in a 9205 oracle database. We can query fine
and the decimal precision is there.
When we query this same view from ms sql server we lose the precision
so 115.25 becomes 115.
does anyone know a workaround for this?
View 2 Replies
View Related
Jan 28, 2006
Right now the database I am working with is storing time inan Integer data type and is storing the time value in seconds.The application does not allow entering seconds. It acceptsminutes and hours.I have a report where it is doing:SELECT SUM(TIMEENTERED)and the SUM is *blowing* up as the SUM is reachingthe BIGINT range.I can fix the problem by changing all codes to:SELECT SUM(CAST(TIMEENTERED AS BIGINT))But now that I ran into this problem I want to find outif storing the time in seconds using INTEGER datatype is the best solution?I've been searching this newsgroup and other placesthe whole day. I even ran into my own three year oldpost. Three years ago my problem was data migrationrelated and now it is more of performance related thananything else.http://groups.google.com/groups?as_...y=2006&safe=offI could not find this specific topic in SQL books likeSQL for Smarties 2005 by Joe Celko (very good stuff ontemporal topics but nothing specific to my question),or Inside SQL Server 2000.Which data type would be ideal and why?smalldatetime?integer?decimal?float?The type of operations that are being done in the databaseare:1- Entering time in hours on work done on a taskFor the data entry part, the application accepts2.5 as 2 and a half hours and it is storing2.5 * 3600 = 9000 seconds.It also accepts entering 2:30 as 2 hours and30 minutes and again storing 9000 seconds.I even saw a page where you can enter clocktime: I worked from 9:30AM to 12:45PMas an exampleWhen i checked the underlying table(s) I sawthat the ENTEREDTIME is always the durationin seconds. So the data entry can either be2.5 hours where ENTEREDTIME = 9000 secondsor9:00AM to 11:30AMwhere STARTDATE is today's date for examplestored as 1/27/2005 09:00AMand where ENTEREDTIME = 9000 seconds2- All kinds of reports showing total time in hoursfor example: Project1 = 18.5 hoursThe code in the SP are all like:SUM(ENTEREDTIME) / CAST(3600 AS DECIMAL(6,2))AS TOTALTIME3- I am sure a lot of other arithmetic calculations arebeing done with this ENTEREDTIME field.What would be the best way to store hours/minutesbased on how we are using Time in the database?Either I will stick with Integer but store in minutestime instead of calculating in seconds and most likelyupdate all the SUM(ENTEREDTIME) toSUM(CAST(ENTEREDTIME AS BIGINT))or I will switch to storing in decimal/float andmaybe avoid doing :SUM(ENTEREDTIME) / CAST(3600 AS DECIMAL(6,2))AS TOTALTIMEsince the ENTEREDTIME would already be storedin hours time.or I will use DATETIME since in the cases ofI worked from 9:00AM to 11:30AMI have to have a separate column to store the date also.I am a little confused I am hoping I will get some helpfrom you and maybe if I can't find the best solution, atleast eliminate the NOT so good ones I am thinking of.Thank you
View 1 Replies
View Related
Nov 13, 2007
I'm connecting MSSQL Server 2005 Express via MS Driver for PHP (CTP version October 2007) and sometimes I don't retrieve exact float values. For example, in database is 0.7 and I get 0.69999999999999996, but for 1.0 in database I get 1.0. The result is the same, if I use prepared statement (sqlsrv_conn_prepare() and sqlsrv_stmt_execute()) or directly sql_conn_execute().
The table definition is very simple:
CREATE TABLE [dbo].[test](
a float NOT NULL
);
insert into test values(0.7);
insert into test values(1.0);
insert into test values(1.1);
insert into test values(1.2);
insert into test values(1.3);
insert into test values(1.4);
insert into test values(1.5);
insert into test values(1.6);
insert into test values(1.7);
insert into test values(1.8);
insert into test values(1.9);
insert into test values(2.0);
And select command is:
select a from test;
PHP code:
$conn = sqlsrv_connect("localhostsqlexpress", array("UID" => "sa", "PWD" => "password"));
sqlsrv_conn_execute($conn, "USE dbname");
$stmt = sqlsrv_conn_execute($conn, "select a from test");
while($row = sqlsrv_stmt_fetch_array($stmt, SQLSRV_FETCH_TYPE_ARRAY))
echo $row[1]."; ";
Result:
0.69999999999999996; 1.0; 1.1000000000000001; 1.2; 1.3; 1.3999999999999999; 1.5; 1.6000000000000001; 1.7; 1.8; 1.8999999999999999; 2.0
Expected result:
0.7; 1.0; 1.1; 1.2; 1.3; 1.4; 1.5; 1.6; 1.7; 1.8; 1.9; 2.0
Running configuration:
Windows 2003 Server SP2, SQL Server 2005 Express SP2, IIS6, PHP 5.2.5 ISAPI
Thank you very much for help.
Vlasta
View 4 Replies
View Related
Apr 17, 2008
Hello,
Data is being trnasferred from an Oracle view to a SQL Server 2005 table.
Decimals can be previewed in the from the "SQL Command Text Window" but the columns in the target table which are defined as float shows the data being rounded to zero decimal places.
For the Data Source the always use default code page is selected.
Is there a way to retain the decimals?
Thank you,
Rod
View 3 Replies
View Related
Mar 3, 2008
Hello!
Iīm having some trouble converting values represented as strings to the decimal data type.
I have a flat file source, from where I read some currency rates represented without decimals.
Before sending those values to my SQL Server destination, I want to convert them to represent correct values.
An example to clarify:
If my source contains a column named "curr_rate" with the value 000092500 I want to send it
to my destination as 9,2500.
So I set up a Dervied column component, converting my value like so:
((DT_NUMERIC,9,4)curr_rate)/10000
My problems is that the precision is lost, and all thatīs sent to my destination table is 9,0000.
How should I go about to convert my strings without losing precision in the process?
Thanks in advance!
View 4 Replies
View Related
Dec 4, 2013
I am trying to understand why SQL Server gives me significantly lower precision than many of the other sources that have tried when using the POWER function. My environment is 2008 R2 SP2 (10.50.4000.0 X64, Standard edition) on Windows 2008 X64 SP1
DECLARE @x FLOAT(53) = 1.0004;
DECLARE @y FLOAT(53) = 1.0/12.0;
SELECT POWER(@x,@y)-1; -- Answer: 3.33270904724348E-05
GO
DECLARE @x FLOAT(24) = 1.0004;
DECLARE @y FLOAT(24) = 1.0/12.0;
[URL] ....
Answer: 3.33272237835747E-05
I also tried using Windows Calculator.Answer:3.3327223783495255846580902358195e-5
And an online "high-precision" calculator from [URL] ...
Answer: 3.332722378349525584658E-5
The best SQL results compare only to the fourth digit with other results. Everything else agrees to the 10th or 12th digit.
Is the precision of arithmetic calculations on SQL Server that bad, or am I doing something wrong here? Is there another more precise alternative?
I did the following. The results speak for themselves
DECLARE @x FLOAT(53) = 1.0004;
DECLARE @y FLOAT(53) = 1.0/12.0;
DECLARE @z FLOAT(53) = POWER(@x,@y)-1; -- Answer: 3.33270904724348E-05
SELECT POWER((1+@z),12); -- 1.00039999839968
-- using results from other sources (c#, windows calc, casio.com)
SELECT POWER((1+3.33272237835747E-05),12) -- 1.0004
SELECT POWER((1+3.3327223783495255846580902358195e-5),12)-- 1.0004
SELECT POWER((1+3.332722378349525584658E-5),12) -- 1.0004
It is not important what I am trying to do, but in case it will work, I am trying to calculate the monthly return for a 90-day T-bill given the compounded annual return. x is the annualized return.
View 7 Replies
View Related
Apr 12, 2007
I'm reading the MS Training Kit for SQL 2005 Admin, Exam 70-431, (no debating certs, please), and I come across this gem, discussing numeric data types such as bigint, int, smallint, decimal, numeric, etc...
"The storage is also precisely defined, so any data stored in these data types returns and calculates to the same value on either an Intel or an AMD processor and architecture."
So exactly how precise would you have to be to notice a difference between architectures?
View 2 Replies
View Related
Oct 26, 2006
Hi All,
i'm using a "data conversion" object to convert a numeric field to a string just before i save the record set to the database.
the problem is when this numeric field is > 0 it looses the precision on its decimal value.
example, if numeric value is 0.32
after converting this to a string, the new value will be : .32
it's lost the 0 infront of it. i can't do this converion in the query level because it's a derived field, so i need to convert it to a string before stroing it.
when converting to string i'm using the code page 1252 (ANSI - Latin I). i also tried with unicode string both looses this 0 infront.
can any one help on this?
View 7 Replies
View Related
Feb 8, 2007
I am using SQL CLR Integration to create a series of stored procedures.
I am building and deploying from Visual Studio 2005 SP1 and everything is working well except for my stored procedures that have a SqlDecimal typed input argument. By default, the precision and scale of the SqlDecimal is deployed to SqlServer as (18,0).
How can I change this default?
This is an example of my stored procedure definition:
namespace Microsoft.Hurley.DataStore
{
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure(Name = DB.PROC.TEST.INSERT)]
public static Int32 insertTest(out SqlInt16 cTestID, SqlInt16 cOrgID, SqlString cName, SqlDecimal cPassPercentage, SqlByte cNumberQuestionsToDisplay, SqlByte cMaxNumberAttempts, SqlBoolean cIsActive)
{
...
return returnValue;
}
}
}
From SQL Server after the procedure is deployed:
[dbo].[insertTest]
@cTestID [smallint] OUTPUT,
@cOrgID [smallint],
@cName [nvarchar](4000),
@cPassPercentage [numeric](18, 0),
@cNumberQuestionsToDisplay [tinyint],
@cMaxNumberAttempts [tinyint],
@cIsActive [bit]
Thanks!
View 4 Replies
View Related